Python MySQL Database¶

Python can be used in database applications. One of the most popular databases is MySQL. Python needs a MySQL driver to access the MySQL database.

pip install mysql-connector-python

MySQL Database¶

To be able to experiment with the code examples in this tutorial, you should have MySQL installed on your computer.

You can download XAMPP a free MySQL database at https://www.apachefriends.org/download.html. After download the XAMPP Start Apache and MySQL Server.

Test MySQL Connector¶

import mysql.connector

Note: If the above code was executed with no errors, “MySQL Connector” is installed and ready to be used.

Python MySQL Create Database¶

To create a database in MySQL, use the “CREATE DATABASE” statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd=""
)
mycursor = mydb.cursor()
mycursor.execute("create database itronix")

If the above code was executed with no errors, you have successfully created a database

Check if Database Exists¶

You can check if a database exist by listing all databases in your system by using the “SHOW DATABASES” statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd=""
)
mycursor = mydb.cursor()
mycursor.execute("show databases")
for x in mycursor:
    print(x)

('information_schema',)
('itronix',)
('mysql',)
('performance_schema',)
('phpmyadmin',)
('ritesh',)

You can also verify in browser by opening phymyadmin: http://localhost/phpmyadmin/

Python MySQL Create Table¶

To create a table in MySQL, use the “CREATE TABLE” statement. Make sure you define the name of the database when you create the connection

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE student (name VARCHAR(255), address VARCHAR(255), Phone int)")

Check if Table Exists¶

You can check if a table exist by listing all tables in your database with the “SHOW TABLES” statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("show tables")
for x in mycursor:
    print(x)

('student',)

Drop Table¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("drop table student")

Creating Table with Primary Key¶

When creating a table, you should also create a column with a unique key for each record.
This can be done by defining a PRIMARY KEY.
We use the statement “INT AUTO_INCREMENT PRIMARY KEY” which will insert a unique number for each record. Starting at 1, and increased by one for each record.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

If the table already exists, use the ALTER TABLE keyword:¶

mycursor.execute(“ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY”)

Python MySQL Insert Into Table¶

To fill a table in MySQL, use the “INSERT INTO” statement.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
name = input("Enter Name:")
address = input("Enter Address:")
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = (name,address)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

Enter Name:Karan
Enter Address:Ludhiana
1 record inserted.

Note: The statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.

Insert Multiple Rows¶

To insert multiple rows into a table, use the executemany() method.
The second parameter of the executemany() method is a list of tuples, containing the data you want to insert:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Kevin', 'Dublin 4'),
  ('Keisha', 'Otawa 4'),
  ('Varun', 'Ontario'),
  ('Priya', 'Delhi'),
  ('Atharv', 'Goa'),
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

5 record inserted.

Get the last Inserted ID¶

You can get the id of the row you just inserted by asking the cursor object.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
name = input("Enter Name:")
address = input("Enter Address:")
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = (name,address)
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)

Enter Name:Sia
Enter Address:Dubai
1 record inserted, ID: 7

Python MySQL Select From Table¶

To select from a table in MySQL, use the “SELECT” statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Ludhiana')
(2, 'Kevin', 'Dublin 4')
(3, 'Keisha', 'Otawa 4')
(4, 'Varun', 'Ontario')
(5, 'Priya', 'Delhi')
(6, 'Atharv', 'Goa')
(7, 'Sia', 'Dubai')

Selecting Columns¶

To select only some of the columns in a table, use the “SELECT” statement followed by the column name(s):

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

('Karan', 'Ludhiana')
('Kevin', 'Dublin 4')
('Keisha', 'Otawa 4')
('Varun', 'Ontario')
('Priya', 'Delhi')
('Atharv', 'Goa')
('Sia', 'Dubai')

Using the fetchone() Method¶

If you are only interested in one row, you can use the fetchone() method.
The fetchone() method will return the first row of the result:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchone()
print(myresult)

('Karan', 'Ludhiana')

Python MySQL Where¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address ='Ludhiana'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Ludhiana')

Wildcard Characters¶

You can also select the records that starts, includes, or ends with a given letter or phrase.

Use the % to represent wildcard characters:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address LIKE '%lud%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Ludhiana')

Prevent SQL Injection¶

When query values are provided by the user, you should escape the values.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
The mysql.connector module has methods to escape query values:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Otawa 4", )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(3, 'Keisha', 'Otawa 4')

Python MySQL Order By¶

Sort the Result

Use the ORDER BY statement to sort the result in ascending or descending order.
The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(6, 'Atharv', 'Goa')
(1, 'Karan', 'Ludhiana')
(3, 'Keisha', 'Otawa 4')
(2, 'Kevin', 'Dublin 4')
(5, 'Priya', 'Delhi')
(7, 'Sia', 'Dubai')
(4, 'Varun', 'Ontario')

ORDER BY DESC¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(4, 'Varun', 'Ontario')
(7, 'Sia', 'Dubai')
(5, 'Priya', 'Delhi')
(2, 'Kevin', 'Dublin 4')
(3, 'Keisha', 'Otawa 4')
(1, 'Karan', 'Ludhiana')
(6, 'Atharv', 'Goa')

Python MySQL Delete From By¶

You can delete records from an existing table by using the “DELETE FROM” statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = 'Otawa 4'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

1 record(s) deleted

Prevent SQL Injection¶

It is considered a good practice to escape the values of any query, also in delete statements.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
The mysql.connector module uses the placeholder %s to escape values in the delete statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = %s"
adr = ("dublin 4", )
mycursor.execute(sql, adr)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

1 record(s) deleted

Python MySQL Update Table¶

You can update existing records in a table by using the “UPDATE” statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = 'Detroit' WHERE address = 'Ludhiana'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

1 record(s) affected

Python MySQL Limit¶

You can limit the number of records returned from the query, by using the “LIMIT” statement:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Detroit')
(4, 'Varun', 'Ontario')
(5, 'Priya', 'Delhi')
(6, 'Atharv', 'Goa')
(7, 'Sia', 'Dubai')

Start From Another Position¶

If you want to return five records, starting from the third record, you can use the “OFFSET” keyword:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(5, 'Priya', 'Delhi')
(6, 'Atharv', 'Goa')
(7, 'Sia', 'Dubai')

Python MySQL Join¶

Join Two or More Tables
You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.
Consider you have a “customers” table and a “product” table:

Create Table Name Product¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE product (id int, prodname VARCHAR(255))")
sql = "INSERT INTO product (id, prodname) VALUES (%s, %s)"
val = [
  ('1', 'iPhone'),
  ('2', 'Charger'),
  ('3', 'Data Cable'),
  ('4', 'Laptop'),
  ('5', 'Pen Drive'),
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

5 record inserted.

Inner Join is same as Join¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers JOIN product ON customers.id = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')

Select Columns as per require¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers JOIN product ON customers.id = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Inner Join¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers INNER JOIN product ON customers.id = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Left Join¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers LEFT JOIN product ON customers.id = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')
(6, 'Atharv', 'Goa', None)
(7, 'Sia', 'Dubai', None)

Python MySQL Right Join¶

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers RIGHT JOIN product ON customers.id = product.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(None, None, None, 'Charger')
(None, None, None, 'Data Cable')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL OUTER or FULL Join¶

You don’t have FULL JOINS on MySQL, but you can sure emulate them using Union

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "SELECT * from customers as t1 LEFT JOIN product as t2 ON t1.id = t2.id \
 UNION SELECT * from customers as t1 RIGHT JOIN product as t2 ON t1.id = t2.id "
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')
(6, 'Atharv', 'Goa', None, None)
(7, 'Sia', 'Dubai', None, None)
(None, None, None, 2, 'Charger')
(None, None, None, 3, 'Data Cable')

Python MySQL Database

Related Articles