MySQL Query In Python - A Beginner's Guide

In this article, we’ll learn how you can run a MySQL query in Python. Many Web Apps are developed using Flask Or Django Frameworks, and these apps can consist of various databases depending upon the use case of the Client.

What’s special about Python is that it can easily integrate some basic databases like SQLite database or Postgresql and SQLAlchemy.

Hence, to make use of this capability of the Python language we are going to learn some basic SQL Operations and develop one small database using a Python script.

Quick Summary

In this tutorial, we’ll learn some basic SQL queries and learn how to use SQL with Python. We’ll go through queries like CREATE DATABASE, CREATE TABLE, SELECT, UPDATE, INSERT INTO, DELETE, etc.

We’ll also go through some basic python expressions which can be used to integrate and manage SQL using Python scripts. In the end, we’ll conclude by developing a small Python program that will take input an XML file containing some data and convert that data into a meaningful and structured SQL database

Basic SQL Queries

SQL facilitates the following CRUD operations

C: Create
R: Read
U: Update
D: Delete

Now let’s discuss some most import SQL queries.

1. CREATE DATABASE

This query is used to create a database under which you can build up your tables.

Example:

 CREATE DATABASE [IF NOT EXISTS] database_name

Note: [IF NOT EXISTS] is used to check to avoid recreating a database and losing the data stored in it.

2. CREATE TABLE

This query is used to create a table.

Example:

CREATE TABLE [IF NOT EXISTS] table_name

3. INSERT INTO

This query is used to insert some data into a table

Example:

INSERT INTO Users(column_1, column_2) VALUES (value_1, value_2)

4. SELECT

This query is used to extract/select some data out of the table

Example:

SELECT id FROM Genre WHERE name = genre

Here we are selecting the “id” column from the Table “Genre” where the name column = “Some_Value”

5. UPDATE

This query is used to update some previously inserted entry in a table

Example:

UPDATE table_name SET (column_1, column_2) VALUES (value_1, value_2)

6. DELETE

This query is used to delete some data from a table

Example:

DELETE FROM table_name WHERE condition

How to Run a MySQL Query in Python

SQLite3 library is already there which allows the usage of SQL in Python, this library contains some of the most common and the most important functions to help us manage our database through Python scripts.

import sqlite3

conn = sqlite3.connect('trackdb.sqlite')
cur = conn.cursor()

Here the import statement imports the sqlite3 library. conn is like a connection between our database, here the connection is established by using the function: connect(‘database path’). In this case, the name of the database is ‘trackdb.sqlite’ and it is located in the same directory in which the python script is present

Now when the connection is successfully established, we can start executing SQL queries or even SQL scripts in our program. This can be done via the following functions:

executescript(‘sql_script’): This function takes a SQL script string as input and most of the time and execute the entire SQL script this function is used to define the schema of the database
execute(‘sql_query’): This function takes a single SQL query as input and executes it. This function is mostly used for carrying out update, delete, read and other operations.

Sample Code for Running MySQL Queries in Python

#!/usr/bin/python
# -*- coding: utf-8 -*-
import xml.etree.ElementTree as ET
import sqlite3

conn = sqlite3.connect('trackdb.sqlite')
cur = conn.cursor()

# Make some fresh tables using executescript()

cur.executescript('''
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Genre;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Track;

CREATE TABLE Artist (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE
);

CREATE TABLE Genre (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE
);

CREATE TABLE Album (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    artist_id INTEGER,
    title TEXT UNIQUE
);

CREATE TABLE Track (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    title TEXT UNIQUE,
    album_id INTEGER,
    genre_id INTEGER,
    len INTEGER,
    rating INTEGER,
    count INTEGER
);
''')

fname = input('Enter file name: ')
if len(fname) < 1:
    fname = 'Library.xml'


# <key>Track ID</key><integer>369</integer>
# <key>Name</key><string>Another One Bites The Dust</string>
# <key>Artist</key><string>Queen</string>

def lookup(d, key):
    found = False
    for child in d:
        if found:
            return child.text
        if child.tag == 'key' and child.text == key:
            found = True
    return None


stuff = ET.parse(fname)
all = stuff.findall('dict/dict/dict')
print ('Dict count:', len(all))
for entry in all:
    if lookup(entry, 'Track ID') is None:
        continue

    name = lookup(entry, 'Name')
    artist = lookup(entry, 'Artist')
    album = lookup(entry, 'Album')
    count = lookup(entry, 'Play Count')
    rating = lookup(entry, 'Rating')
    length = lookup(entry, 'Total Time')
    genre = lookup(entry, 'Genre')

    if name is None or artist is None or album is None:
        continue

    print (
        name,
        artist,
        album,
        count,
        rating,
        length,
        genre,
        )

    cur.execute('INSERT OR IGNORE INTO Artist (name) VALUES ( ? )',
                (artist, ))
    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))
    artist_id = cur.fetchone()[0]

    cur.execute('INSERT OR IGNORE INTO Album (title, artist_id) VALUES ( ?, ? )'
                , (album, artist_id))
    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))
    album_id = cur.fetchone()[0]

    cur.execute('INSERT OR IGNORE INTO Genre (name) VALUES ( ?)',
                (genre, ))
    cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, ))
    genre_id = cur.fetchone()[0]

    cur.execute('INSERT OR REPLACE INTO Track (title, album_id, len, rating, count, genre_id) VALUES ( ?, ?, ?, ?, ? , ?)'
                , (
        name,
        album_id,
        length,
        rating,
        count,
        genre_id,
        ))

    conn.commit()

Input

Output

Conclusion

In this article, we learned how to use basic SQL queries, their usage, syntax, and their operation. We further developed a Python script that demonstrated the execution and usage of these queries in Python. We learned all the 4 CRUD(Create, Read, Update and Delete) operations and a little bit of how to use sqlite3 module in Python