Basics of PyMySQL Python library

Download and install the latest version of python from Python Website if not already done.

Setting up virtual environment is not a must. But this is a best practice when working on python projects. Click virtual environment for more details.

Install pymysql using the command: pip install pymysql

Type command pip freeze
Should give an output like:

PyMySQL==1.0.2

To connect to MySQL database using PyMySQL, we have to import pymysql, define connection string and then use pymysql.connect() method. Copy the below code to a python file in your Virtual Environment and run it.

import pymysql 
s = '' #Your server name 
d = '' #Your database name
u = '' #Your login
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
print(conn)

To run the code, use command python filename.py

On running the code should see the below output if connection successful.

pymysql.connections.Connection object at 0x000001F940D9E260

PyMySQL Cursor object represents a database cursor, that we can use to manage data from an SQL query. We can create multiple cursors from same connection. Any changes done to data using one cursor is visible to the other cursor immediately.

To read data from MySQL database, execute the command and use fetchall() method.

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchall():
    print(row)

Insert to table sample below.

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("INSERT INTO TableName (id, name) VALUES (%s, %s)", (someid, 'somename'))
conn.commit()

Update data to table sample below.

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("UPDATE TableName SET name = ? WHERE id = ?", 'somename', someid)
conn.commit()

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("DELETE FROM TableName WHERE id = ?", someid)
conn.commit()

Stored procedure without parameters

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("CALL proc_name()")
conn.commit()

Stored procedure with parameters

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
cursor.execute("CALL proc_name(%s, %s)", p1, p2)
conn.commit()

To get the row count in PyMySQL, just provide a variable for execute statement which will get the number of rows of the executed query. Sample below.

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
c = cursor.execute("SELECT * FROM TableName")
print(c)

fetchone() returns next row.
fetchall() returns all remaining rows.

import pymysql
s = '' #Your server(host) name 
d = '' #Your database name
u = '' #Your login user
p = '' #Your login password
conn = pymysql.connect(host=s, user=u, password=p, database=d)
cursor = conn.cursor()
c = cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchone():
    print(row)
for row in cursor.fetchall():
    print(row)

commit() Commits all transactions since last commit or rollback.
rollback() Rolls back all uncommitted transactions.

Make sure to close connection after use. Use conn.close()