Python MySql - Quick Guide

---

---

Python MySql - Overview

The Python standard for database interfaces is the Python DB-API. Most Python database interfaces adhere to this standard.

You can choose the right database for your application. Python Database API supports a wide range of database servers such as −

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

Here is the list of available Python database interfaces: Python Database Interfaces and APIs. You must download a separate DB API module for each database you need to access. For example, if you need to access an Oracle database as well as a MySQL database, you must download both the Oracle and the MySQL database modules.

The DB API provides a minimal standard for working with databases using Python structures and syntax wherever possible. This API includes the following −

• Importing the API module.
• Acquiring a connection with the database.
• Issuing SQL statements and stored procedures.
• Closing the connection

MySQLdb

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API.

Python MySql - Connect to Database

Python MySQLdb provides MySQLdb.connect() function to open a database connection. This function takes multiple parameters and returns a connection object to do database operations.

Syntax

db = mysql.connector.connect(host="localhost",user="root",password="root@123")

There are several other properties as well. Refer MySQL Connector for complete reference.

You can disconnect from the MySQL database anytime using another connection object function close().

Syntax

db.close()

Example - Connecting to MySql Server

Try the following example to connect to a MySQL server −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
if data:
   print('Version available: ', data)
else:
   print('Version not retrieved.')

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

(myenv) D:\Projects\python\myenv>py main.py
Version available:  ('8.0.43',)

Python MySql - Creating Database

Python uses c.execute(q) function to create or delete a MySQL database where c is cursor and q is the query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

Example - Creating a Database

Try the following example to create a database −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="Micro@Soft12")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("CREATE DATABASE TUTORIALS")

print('Database created');

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

Database created

Python MySql - Dropping Database

Python uses c.execute(q) function to create or delete a MySQL database where c is cursor and q is the query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

Example - Dropping a Database

Try the following example to drop a database −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("DROP DATABASE TUTORIALS")

print('Database dropped');

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

Database dropped

Python MySql - Selecting Database

Python MySQLdb provides MySQLdb.connect() function to select a database. This function takes multiple parameters and returns a connection object to do database operations.

Syntax

db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

You can disconnect from the MySQL database anytime using another connection object function close().

Syntax

db.close()

Example

Try the following example to connect to a MySQL database −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="Micro@Soft12", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
if data:
   print('Version available: ', data)
else:
   print('Version not retrieved.')

# disconnect from server
db.close()

Output

Execute the mysql_example.py script using python and verify the output.

Version available:  ('8.0.43',)

Python MySql - Create Tables

Python uses c.execute(q) function to create a table where c is cursor and q is the query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

Example - Creating Table

Try the following example to create a table −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = """CREATE TABLE tutorials_tbl( 
            tutorial_id INT NOT NULL AUTO_INCREMENT,
            tutorial_title VARCHAR(100) NOT NULL,
            tutorial_author VARCHAR(40) NOT NULL,
            submission_date DATE,
            PRIMARY KEY ( tutorial_id )); """;

# execute SQL query using execute() method.
cursor.execute(sql)

print('tutorials_tbl created')

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

tutorials_tbl created

Python MySql - Drop Tables

Python uses c.execute(q) function to drop a table where c is cursor and q is the delete query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

Example - Dropping Table

Try the following example to drop a table −

Copy and paste the following example as mysql_example.ty −

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("Drop Table tutorials_tbl")

print('Table dropped');

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

Table dropped

Python MySql - Insert Records into Table

Python uses c.execute(q) function to insert a record(s) in a table where c is cursor and q is the insert query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

# commit the record
db.commit()

# get the row id for inserted record
print("ID:", cursor.lastrowid)

# print the number of records inserted
print(mycursor.rowcount, "records inserted.")

Example - Inserting records in a table

Try the following example to insert records in a table −

Copy and paste the following example as main.ty −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = """INSERT INTO tutorials_tbl
         (tutorial_title,tutorial_author, submission_date) 
         VALUES ('HTML 5', 'Robert', '2010-02-10'),
         ('Java', 'Julie', '2020-12-10'),
         ('JQuery', 'Julie', '2020-05-10')
         """

# execute SQL query using execute() method.
cursor.execute(sql)

# commit the record
db.commit()

# get the row id for inserted record
print("ID:", cursor.lastrowid)

# print the number of records inserted
print(cursor.rowcount, "records inserted.")

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

ID: 5
3 records inserted.

Python MySql - Selecting Records from a Table

Select/Read Operation on any database means to fetch some useful information from the database.

Once our database connection is established, you are ready to make a query into this database. You can use either fetchone() method to fetch single record or fetchall() method to fetch multiple values from a database table.

• fetchone() − It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.

• fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.

• rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

result = cursor.fetchall()

for record in result:
   print(record)

Example - Selecting Records

Try the following example to select records from a table −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "Select * from tutorials_tbl"

# execute SQL query using execute() method.
cursor.execute(sql)

# fetch all records from cursor
result = cursor.fetchall()

# iterate result and print records
for record in result:
   print(record)

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

(1, 'HTML 5', 'Robert', datetime.date(2010, 2, 10))
(2, 'Java', 'Julie', datetime.date(2020, 12, 10))
(3, 'JQuery', 'Julie', datetime.date(2020, 5, 10))

Python MySql - Updating Records in a Table

Python uses c.execute(q) function to update a record from a table where c is cursor and q is the update query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

cursor.commit()

# get the record count updated
print(mycursor.rowcount, "record(s) affected")

Example - Updating Records in Table

Try the following example to update records in a table −

Copy and paste the following example as main.py −

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "UPDATE tutorials_tbl set tutorial_title = "Learning Java" where tutorial_id = 2"

# execute SQL query using execute() method.
cursor.execute(sql)

db.commit()

# get the record count updated
print(cursor.rowcount, " record(s) affected")

# disconnect from server
db.close()

Output

Execute the mysql_example.py script using python and verify the output.

1 record(s) affected

Python MySql - Deleting Records from a Table

Python uses c.execute(q) function to delete a record from a table where c is cursor and q is the delete query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

cursor.commit()

# get the record count updated
print(mycursor.rowcount, "record(s) affected")

Example - Inserting Records in a Table

Try the following example to insert records in a table −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "Delete from tutorials_tbl where tutorial_id = 2"

# execute SQL query using execute() method.
cursor.execute(sql)

db.commit()

# get the record count updated
print(cursor.rowcount, " record(s) affected")

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

1 record(s) affected

Python MySql - Using where Clause

Python uses c.execute(q) function to select a record(s) conditionally using Where Clause from a table where c is cursor and q is the select query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

result = cursor.fetchall()

for record in result:
   print(record)

Example - Using Where Clause

Try the following example to select records from a table −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "Select * from tutorials_tbl Where tutorial_id = 3"

# execute SQL query using execute() method.
cursor.execute(sql)

# fetch all records from cursor
result = cursor.fetchall()

# iterate result and print records
for record in result:
   print(record)

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

(3, 'JQuery', 'Julie', datetime.date(2020, 5, 10))

Python MySql - Using like Clause

Python uses c.execute(q) function to select a record(s) conditionally using Like Statement from a table where c is cursor and q is the select query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

result = cursor.fetchall()

for record in result:
   print(record)

Example - Using Like Clause

Try the following example to select records from a table using Like Statement −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "Select * from tutorials_tbl Where tutorial_title like 'J%'"

# execute SQL query using execute() method.
cursor.execute(sql)

# fetch all records from cursor
result = cursor.fetchall()

# iterate result and print records
for record in result:
   print(record)

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

(6, 'Java', 'Julie', datetime.date(2020, 12, 10))
(7, 'JQuery', 'Julie', datetime.date(2020, 5, 10))

Python MySql - Sorting Data

Python uses c.execute(q) function to select a record(s) in sorted order using sort by Statement from a table where c is cursor and q is the select query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

result = cursor.fetchall()

for record in result:
   print(record)

Example - Sorting Data

Try the following example to select sorted records from a table using sort by Statement −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "Select * from tutorials_tbl order by tutorial_title asc"

# execute SQL query using execute() method.
cursor.execute(sql)

# fetch all records from cursor
result = cursor.fetchall()

# iterate result and print records
for record in result:
   print(record)

# disconnect from server
db.close()

Output

Execute the mysql_example.py script using python and verify the output.

(1, 'HTML 5', 'Robert', datetime.date(2010, 2, 10))
(2, 'Java', 'Julie', datetime.date(2020, 12, 10))
(3, 'JQuery', 'Julie', datetime.date(2020, 5, 10))

Python MySql - Using Joins

Python uses c.execute(q) function to select a record(s) from a table where c is cursor and q is the select query to be executed.

Syntax

# execute SQL query using execute() method.
cursor.execute(sql)

result = cursor.fetchall()

for record in result:
   print(record)

First create a table in MySQL using following script and insert two records.

create table tcount_tbl(
   tutorial_author VARCHAR(40) NOT NULL,
   tutorial_count int
);

insert into tcount_tbl values('Julie', 2);
insert into tcount_tbl values('Robert', 1);

Example - Using joins

Try the following example to get records from a two tables using Join. −

Copy and paste the following example as main.py −

main.py

import mysql.connector

# Open database connection
db = mysql.connector.connect(host="localhost",user="root",password="root@123", database="TUTORIALS")

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = """SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
				FROM tutorials_tbl a, tcount_tbl b
				WHERE a.tutorial_author = b.tutorial_author"""

# execute SQL query using execute() method.
cursor.execute(sql)

# fetch all records from cursor
result = cursor.fetchall()

# iterate result and print records
for record in result:
  print(record)

# disconnect from server
db.close()

Output

Execute the main.py script using python and verify the output.

(1, 'Robert', 1)
(2, 'Julie', 2)
(3, 'Julie', 2)

Python MySql - Using Transactions

Transactions are a mechanism that ensures data consistency. Transactions have the following four properties −

• Atomicity − Either a transaction completes or nothing happens at all.

• Consistency − A transaction must start in a consistent state and leave the system in a consistent state.

• Isolation − Intermediate results of a transaction are not visible outside the current transaction.

• Durability − Once a transaction was committed, the effects are persistent, even after a system failure.

The Python DB API 2.0 provides two methods to either commit or rollback a transaction.

Example

You already know how to implement transactions. Here is again similar example −

# Prepare SQL query to DELETE required records
sql = "Delete from tutorials_tbl where tutorial_id = 2"
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

COMMIT Operation

Commit is the operation, which gives a green signal to database to finalize the changes, and after this operation, no change can be reverted back.

Here is a simple example to call commit method.

db.commit()

ROLLBACK Operation

If you are not satisfied with one or more of the changes and you want to revert back those changes completely, then use rollback() method.

Here is a simple example to call rollback() method.

db.rollback()

Disconnecting Database

To disconnect Database connection, use close() method.

db.close()

If the connection to a database is closed by the user with the close() method, any outstanding transactions are rolled back by the DB. However, instead of depending on any of DB lower level implementation details, your application would be better off calling commit or rollback explicitly.

Python MySql - Handling Errors

There are many sources of errors. A few examples are a syntax error in an executed SQL statement, a connection failure, or calling the fetch method for an already canceled or finished statement handle.

The DB API defines a number of errors that must exist in each database module. The following table lists these exceptions.

Your Python scripts should handle these errors, but before using any of the above exceptions, make sure your MySQLdb has support for that exception. You can get more information about them by reading the DB API 2.0 specification.