How to Use PyMySQL in Python: Connect and Query MySQL

To use pymysql in Python, first install it with pip install pymysql. Then, import pymysql, create a connection to your MySQL database using pymysql.connect(), and use a cursor to execute SQL queries.

Syntax

Here is the basic syntax to connect to a MySQL database and run a query using pymysql:

• pymysql.connect(): Opens a connection to the database with parameters like host, user, password, and database name.
• connection.cursor(): Creates a cursor object to execute SQL commands.
• cursor.execute(): Runs an SQL query.
• cursor.fetchall(): Retrieves all rows from the query result.
• connection.commit(): Saves changes for insert/update/delete queries.
• connection.close(): Closes the database connection.

python

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

cursor = connection.cursor()
cursor.execute('YOUR SQL QUERY HERE')
results = cursor.fetchall()

connection.commit()
connection.close()

Example

This example connects to a MySQL database, creates a table, inserts a row, and fetches the data to print it.

python

import pymysql

# Connect to the database
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='password123',
    database='testdb'
)

try:
    cursor = connection.cursor()
    # Create a table
    cursor.execute('CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))')
    
    # Insert a row
    cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
    connection.commit()

    # Query the table
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()

    for row in rows:
        print(row)
finally:
    connection.close()

Output

(1, 'Alice')

Common Pitfalls

Common mistakes when using pymysql include:

• Not closing the connection, which can cause resource leaks.
• Forgetting to call connection.commit() after insert/update/delete queries, so changes are not saved.
• Using incorrect connection parameters like wrong host, user, or password.
• Not handling exceptions, which can crash your program if the database is unreachable.

python

import pymysql

# Wrong way: missing commit and close
connection = pymysql.connect(host='localhost', user='root', password='wrongpass', database='testdb')
cursor = connection.cursor()
cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
# No commit or close here

# Right way:
try:
    connection = pymysql.connect(host='localhost', user='root', password='password123', database='testdb')
    cursor = connection.cursor()
    cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
    connection.commit()
finally:
    connection.close()

Quick Reference

Here is a quick cheat sheet for common pymysql methods:

Method	Description
pymysql.connect()	Connect to MySQL database
connection.cursor()	Create a cursor to execute queries
cursor.execute(sql)	Run an SQL query
cursor.fetchall()	Get all rows from query result
connection.commit()	Save changes to database
connection.close()	Close the database connection

Key Takeaways

Install pymysql with pip before using it in your Python code.

Always call connection.commit() after modifying data to save changes.

Close your database connection to free resources.

Use try-finally or context managers to handle connections safely.

Check your connection parameters carefully to avoid connection errors.