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.
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.
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.
CREATE TABLE [IF NOT EXISTS] table_name
3. INSERT INTO
This query is used to insert some data into a table
INSERT INTO Users(column_1, column_2) VALUES (value_1, value_2)
This query is used to extract/select some data out of the table
SELECT id FROM Genre WHERE name = genre
Here we are selecting the “id” column from the Table “Genre” where the name column = “Some_Value”
This query is used to update some previously inserted entry in a table
UPDATE table_name SET (column_1, column_2) VALUES (value_1, value_2)
This query is used to delete some data from a table
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() 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() cur.execute('INSERT OR IGNORE INTO Genre (name) VALUES ( ?)', (genre, )) cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, )) genre_id = cur.fetchone() 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()
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
To learn more about using SQL with Python, you can refer to the following link:
- SQL Official Documentation