SQLite and MySQL with Python

by Charlie Jackson

Posted on April 12, 2019 at 19:00 PM

a sql table showing user data

In this tutorial we'll be going through the basics of using Python with an SQLite database as well as MySQL. We'll be hosting the MySQL database locally so if you'd like to follow along you can download XAMP. For SQLite we'll be using a DB viewer for SQLite so again download if required. I also have a previous tutorial on SQL basics with SQLite.

Databases using SQLite and Python

SQLite comes pre-installed with Python so we simply need to import the 'sqlite3' package at the top of your Python script and we can start using it. After importing we must connect to our SQLite database - this is a case calling the sqlite3 method 'connect' as well as creating a 'cursor' object. Note, when we connect to the specified database if will first look for an existing file and if it cannot find an existing database it will create a new database. We can do some simple 'insert' statements - in the example I have stored in a list and looped through. Finally we commit the executions so everything is saved.

import sqlite3

try:
    # connect to database
    db = sqlite3.connect('example_db.db')
    cursor = db.cursor()

    # Create database
    cursor.execute("""CREATE TABLE IF NOT EXISTS Persons (PersonID INTEGER PRIMARY KEY AUTOINCREMENT,FirstName varchar(255),LastName varchar(255));""")

    # Insert data into database
    sql_statements = ["INSERT INTO Persons (FirstName,LastName) VALUES ('Charlie','Jackson');", "INSERT INTO Persons (FirstName,LastName) VALUES ('Scott','Jackson');", "INSERT INTO Persons (FirstName,LastName) VALUES ('Dave','Jackson');","INSERT INTO Persons (FirstName,LastName) VALUES ('Audrey','Plastiras');","INSERT INTO Persons (FirstName,LastName) VALUES ('Nikki','Plastiras');","INSERT INTO Persons (FirstName,LastName) VALUES ('Dave','Plastiras');","INSERT INTO Persons (FirstName,LastName) VALUES ('Grace','Plastiras');"]
    for statement in sql_statements:
        print(statement)
        cursor.execute(statement)

    db.commit()

except Exception as e:
    print("Connection failed: ", e)

SQLite insert statement

To pass in a variable into your SQL statement use the '?' as a placeholder. For this example I have created a function which accepts a user in the form of a tuple (first name and last name). Now we can simply call the 'InsertUser' function with a tuple of first name and last name to add new users. Alternatively, you do the run the same code in oneline which is commented out.

def InsertUser(User):
    SQL_Insert = """INSERT INTO Persons (FirstName,LastName) VALUES (?,?)"""
    cursor.execute(SQL_Insert, User)
    #cursor.execute("INSERT INTO Persons (FirstName,LastName) VALUES (?,?)", (User[0],User[1],))
    print(User[0] + " " + User[1] + " inserted into database")

InsertUser(('Graihagh', 'Jackson')) # note we have passed in a tuple
InsertUser(('Heath', 'Jackson'))

SQL select statement

To run a select statement we follow a similar process - we include create a function with one parameter which accepts a last name. We then pass the 'LastName' into the executable SQL statement, fetch all the results and return them. Note the returned data is a list of tuples.

def SelectUserLastName(LastName):
    cursor.execute("SELECT * FROM Persons WHERE Lastname = ?", (LastName,))
    Users = cursor.fetchall()
    return (Users)

SelectUserLastName('Jackson')
SelectUserLastName('Plastiras')

SQL update statement

To update a row we use a 'update' statement and specify a 'where' clause and 'set' the new value. Again, I have wrapped this all into a function which accepts the first name. Note this is not very dynamic i.e. it always changes the last name to 'Jackson' but for the purpose of showing the basics in Python it does the trick.

def UpdateUser(FirstName):
    cursor.execute("UPDATE Persons SET LastName = 'Jackson' WHERE FirstName = ?;", (FirstName,))
    print(FirstName + " user profile updated")

UpdateUser('Audrey')

SQL delete statement

def DeleteUsers(LastName):
    cursor.execute("DELETE FROM Persons WHERE LastName = ?;", (LastName,))
    print(LastName + " deleted from table")

DeleteUsers('Jackson')

SQLite code takeaway

This code can be saved in a python file (.py) and executed in the terminal.

import sqlite3

def InsertUser(User):
    """Insert new users"""
    SQL_Insert = """INSERT INTO Persons (FirstName,LastName) VALUES (?,?)"""
    cursor.execute(SQL_Insert, User)
    #cursor.execute("INSERT INTO Persons (FirstName,LastName) VALUES (?,?)", (User[0],User[1],))
    print(User[0] + " " + User[1] + " inserted into database")

def SelectUserLastName(LastName):
    """Select users based upon last name. Returns a list of tuples"""
    cursor.execute("SELECT * FROM Persons WHERE Lastname = ?", (LastName,))
    Users = cursor.fetchall()
    return (Users)

def UpdateUser(FirstName):
    """Set a new last name to 'Jackson' when where clause evaluates true"""
    cursor.execute("UPDATE Persons SET LastName = 'Jackson' WHERE FirstName = ?;", (FirstName,))
    print(FirstName + " user profile updated")

def DeleteUsers(LastName):
    """Delete users based upon last name"""
    cursor.execute("DELETE FROM Persons WHERE LastName = ?;", (LastName,))
    print(LastName + " deleted from table")

try:
    # Connect to database
    db = sqlite3.connect('example_db.db') # this will either connect to a existing database or create a new one if none was found
    cursor = db.cursor()

    # Create database
    cursor.execute("""CREATE TABLE IF NOT EXISTS Persons (PersonID INTEGER PRIMARY KEY AUTOINCREMENT,FirstName varchar(255),LastName varchar(255));""")

    # Insert data into database
    sql_statements = ["INSERT INTO Persons (FirstName,LastName) VALUES ('Charlie','Jackson');", "INSERT INTO Persons (FirstName,LastName) VALUES ('Scott','Jackson');", "INSERT INTO Persons (FirstName,LastName) VALUES ('Dave','Jackson');","INSERT INTO Persons (FirstName,LastName) VALUES ('Audrey','Plastiras');","INSERT INTO Persons (FirstName,LastName) VALUES ('Nikki','Plastiras');","INSERT INTO Persons (FirstName,LastName) VALUES ('Dave','Plastiras');","INSERT INTO Persons (FirstName,LastName) VALUES ('Grace','Plastiras');"]
    for statement in sql_statements:
        print(statement)
        cursor.execute(statement)

    InsertUser(('Graihagh', 'Jackson')) # note we have passed in a tuple
    InsertUser(('Heath', 'Jackson'))

    SelectUserLastName('Jackson')
    SelectUserLastName('Plastiras')

    UpdateUser('Audrey')

    DeleteUsers('Jackson')

    db.commit()

except Exception as e:
    print("failed: ", e)

Databases using MySQL and Python

As noted previously we are going to connect to a MySQL database via a XAMP - so you will need to download XAMP. We're also using 'mysql.connector' package which needs to be installed - this can be down via pip:

pip install mysql-connector-python

Once XAMP is installed, head over to the 'https://localhost' in your web browser and then to the tab 'phpMyAdmin'. From here we are going to create an example database - use the 'SQL' tab enter the SQL below and hit 'go'.

CREATE DATABASE example_db;
a sql table showing user data

Now we have a working database, we'll make our connection using 'mysql.connector'. To connect we'll have to provide a host, some login details and the database. As we are using XAMP, the hostname is 'locahost', we'll use the default log in details which are 'root' for the username and nothing for the password. Finally we'll use the database we've just created named 'example_db'. If that all connects, then we create a new table which has 3 columns - an ID ,first name and a last name.

hostname = 'localhost'
username = 'root'
password = ''
database = 'example_db'

import mysql.connector

try:
    myConnection = mysql.connector.connect(host=hostname, user=username, passwd=password, db=database)
    cursor = myConnection.cursor()

    # Create database
    cursor.execute("""CREATE TABLE IF NOT EXISTS Persons (Personid int NOT NULL AUTO_INCREMENT,FirstName varchar(255),LastName varchar(255) NOT NULL,PRIMARY KEY (Personid));""")

		myConnection.commit()
    myConnection.close()

except Exception as e:
    print("Failed: " + str(e))

The CRUD functionality between SQLite and MySQL is marginal so for the remaining examples I have provided the code which is near identical.

MySQL insert statement

As with the SQLite example before, we create a function which accepts a user (in our case we pass a tuple with a first and last name). The only difference this time is instead of a '?' which use '%s' to pass variables into the SQL statement.

def InsertUser(User):
    SQL_Statement = "INSERT INTO Persons (FirstName,LastName) VALUES (%s, %s)"
    cursor.execute(SQL_Statement, (User[0],User[1],))
    print(User[0] + " " + User[1] + " inserted into database")

InsertUser(('Graihagh', 'Jackson')) # note we have passed in a tuple
InsertUser(('Heath', 'Jackson'))

MySQL select statement

def SelectUserLastName(LastName):
    SQL_Statement = "SELECT * FROM Persons WHERE Lastname = %s"
    cursor.execute(SQL_Statement, (LastName,))
    Users = cursor.fetchall()
    return Users

SelectUserLastName('Jackson')

MySQL update statement

def UpdateUser(FirstName):
    SQL_Statement = "UPDATE Persons SET LastName = 'Alcock' WHERE FirstName = %s;"
    cursor.execute(SQL_Statement, (FirstName,))
    print(FirstName + " user profile updated")

UpdateUser('Heath')

MySQL delete statement

def DeleteUsers(LastName):
    SQL_Statement = "DELETE FROM Persons WHERE LastName = %s;"
    cursor.execute(SQL_Statement, (LastName,))
    print(LastName + " deleted from table")

DeleteUsers('Jackson')

MySQL code takeaway

hostname = 'localhost'
username = 'root'
password = ''
database = 'example_db'

import mysql.connector

def InsertUser(User):
    SQL_Statement = "INSERT INTO Persons (FirstName,LastName) VALUES (%s, %s)"
    cursor.execute(SQL_Statement, (User[0],User[1],))
    print(User[0] + " " + User[1] + " inserted into database")

def SelectUserLastName(LastName):
    SQL_Statement = "SELECT * FROM Persons WHERE Lastname = %s"
    cursor.execute(SQL_Statement, (LastName,))
    Users = cursor.fetchall()
    return Users

def UpdateUser(FirstName):
    SQL_Statement = "UPDATE Persons SET LastName = 'Alcock' WHERE FirstName = %s;"
    cursor.execute(SQL_Statement, (FirstName,))
    print(FirstName + " user profile updated")

def DeleteUsers(LastName):
    SQL_Statement = "DELETE FROM Persons WHERE LastName = %s;"
    cursor.execute(SQL_Statement, (LastName,))
    print(LastName + " deleted from table")


try:
    myConnection = mysql.connector.connect(host=hostname, user=username, passwd=password, db=database)
    cursor = myConnection.cursor()

    # Create database
    cursor.execute("""CREATE TABLE IF NOT EXISTS Persons (Personid int NOT NULL AUTO_INCREMENT,FirstName varchar(255),LastName varchar(255) NOT NULL,PRIMARY KEY (Personid));""")

    InsertUser(('Graihagh', 'Jackson')) # note we have passed in a tuple
    InsertUser(('Heath', 'Jackson'))

    SelectUserLastName('Jackson')

    UpdateUser('Heath')

    DeleteUsers('Jackson')

    myConnection.commit()
    myConnection.close()

except Exception as e:
    print("Failed: " + str(e))
Categories
Python programming

SEO
Web development
Popular posts