Previous Index Next

Interface of Python with SQL Database

Python can be used to create database application in MySQL. To access MySQL database with Python you need to install MySQL driver.

Python comes with an inbuilt package management system, pip. Pip can install, update, or delete any official package. You can install MySQL Connector via the command line (cmd) by entering:

python -m pip install mysql-connector-python

To test if the installation was successful, type the following command on shell:

>>> import mysql.connector

Connecting to MySQL Using Connector

To interact with a MySQL server first you need to establish a connection. To do this, you need to call connect() constructor that creates a connection to the MySQL server and returns a MySQLConnection object. The following coding examples illustrate this:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

What is a cursor?

A database cursor is a mechanism that enables retrieval, insertion and removal of database records. Cursors are used to process rows / result set returned by SQL queries.

The MySQLCursor class instantiates objects that can execute operations such as SQL statements. Cursor objects interact with the MySQL server using a MySQLConnection object.

To create a cursor, use the cursor() method of a connection object:

mycursor = mydb.cursor()

MySQLConnection class methods and properties

MySQLCursor.execute()

This method executes the given database query. Tuple or dictionary is passed as parameter.

MySQLCursor.fetchall()

This method retrieves all the rows in the result set of a query and returns them as list of tuples.

MySQLCursor.fetchone()

This method retrieves first row in the result set of a query and returns them as tuple.

MySQLCursor.rowcount Property

Returns the number of rows in case of SELECT, DELETE and UPDATE operations.

Database Application Using MySQL and Python

Following examples illustrate how to develop Python applications which connect to MySQL Server using MySQL Connector/Python. First create a database 'school' and a table 'student' having following structure.

Field	Type	Null	Key
rno	int	NO	PRI
name	char(30)	YES
fees	int	YES
class	char(3)	YES

MySQL Code for creating database and table

mysql> create database school;
Query OK, 1 row affected (0.13 sec)

mysql> use school;
Database changed
mysql> create table student
    -> (
    -> rno int primary key,
    -> name char(30),
    -> fees int,
    -> class char(3)
    -> );
Query OK, 0 rows affected (0.23 sec)

Inserting Record Using Connector

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="school"
)

mycursor = mydb.cursor()
sql = "INSERT INTO student(rno, name, fees, class) VALUES (%s, %s, %s, %s)"
val = ("2", "Ananya","7800","K12")
mycursor.execute(sql, val)
mydb.commit()

print(mycursor.rowcount, "record inserted.")
mycursor.close()
mydb.close()

Delete Record Using Connector

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="school"
)

mycursor = mydb.cursor()
sql = "DELETE FROM student WHERE rno = %s"
val = ("1",)
mycursor.execute(sql, val)
mydb.commit()

print(mycursor.rowcount, "record deleted.")
mycursor.close()
mydb.close()

Update Record Using Connector

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="school"
)

mycursor = mydb.cursor()
sql = "UPDATE student SET class = %s WHERE rno = %s"
val = ("11","1")
mycursor.execute(sql, val)
mydb.commit()

print(mycursor.rowcount, "record updated.")
mycursor.close()
mydb.close()

Display Records Using Connector

Select all records from the "student" table, and display the result:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="1234",
  database="school"
)

mycursor = mydb.cursor()
sql = "select * from student"
mycursor.execute(sql)
data = mycursor.fetchall()
for row in data:
    print(row[0],row[1],row[2],row[3])

mycursor.close()
mydb.close()

Contact Book - A Complete Python Project Using MySql

Some of the features in this project to insert contacts, delete contacts, update contact information, and display saved contacts. Contact details are saved in mysql database. view project

Previous Index Next