XQA
Back to Python
2025-12-275 min read

MYSQL (Python Programming)

Learn MYSQL (Python Programming) step by step with clear examples and exercises.

Title: Python MySQL Interaction: A full guide for Practical Depth

Why This Matters

Python MySQL interaction is crucial for any data-driven project, enabling seamless database management and efficient data retrieval or manipulation. Understanding how to work with MySQL databases in Python can significantly boost your problem-solving abilities, making you a valuable asset in the tech industry. This lesson will guide you through practical examples, common mistakes, and frequently asked questions to help you master this essential skill.

Prerequisites

Before diving into Python MySQL interaction, ensure you have a solid understanding of:

  1. Basic Python syntax and data structures (variables, loops, functions)
  2. SQL basics (tables, queries, indexes, joins)
  3. Familiarity with the mysql-connector-python library
  4. Installation of the necessary libraries (Python's mysql-connector-python package)

Core Concept

To interact with MySQL databases in Python, you'll use the mysql-connector-python library. First, let's install it using pip:

pip install mysql-connector-python

Now, let's create a simple example by connecting to a database and executing queries:

import mysql.connector

Establish the connection

mydb = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword",

database="mydatabase"

)

Create a cursor object

mycursor = mydb.cursor()

Execute a query

mycursor.execute("SELECT * FROM customers")

Fetch all the rows as a list of tuples

myresults = mycursor.fetchall()

for x in myresults:

print(x)


Replace `localhost`, `yourusername`, `yourpassword`, and `mydatabase` with your MySQL server details. This script connects to the specified database, executes a query that selects all records from the customers table, fetches the results, and prints them out.

### Connecting to Multiple Databases

To connect to multiple databases within the same script, simply create separate connections for each database:

mydb1 = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword",

database="database1"

)

mydb2 = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword",

database="database2"

)


### Closing Connections

Always close your connections after you're done using them with `mydb.close()`.

Worked Example

Let's create a new database, a table within it, insert some data into the table, and then retrieve it using Python:

import mysql.connector

Create a connection object

mydb = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword"

)

Create a cursor object

mycursor = mydb.cursor()

Create a database

mycursor.execute("CREATE DATABASE mydatabase")

Select the newly created database

mydb = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword",

database="mydatabase"

)

Create a table in the database

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

Insert data into the table

mycursor.execute("INSERT INTO customers (name, address) VALUES ('John', 'Highway 21')")

mycursor.execute("INSERT INTO customers (name, address) VALUES ('Sara', 'Lowstreet 4')")

Commit the transaction

mydb.commit()

Close the connection

mydb.close()

Reconnect to the database and execute a query

mydb = mysql.connector.connect(

host="localhost",

user="yourusername",

password="yourpassword",

database="mydatabase"

)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresults = mycursor.fetchall()

for x in myresults:

print(x)


This script creates a new database, a table within it, inserts two records, and then retrieves and prints the data using Python.

Common Mistakes

  1. Not importing the mysql-connector-python library: Make sure to include import mysql.connector at the beginning of your scripts.
  2. Incorrect connection details: Double-check that your host, username, password, and database names are correct.
  3. Forgetting to commit transactions: After executing SQL commands like INSERT or UPDATE, don't forget to call mydb.commit() to save the changes.
  4. Not closing connections: Always close your connections after you're done using them with mydb.close().
  5. Syntax errors in SQL queries: Ensure that your SQL syntax is correct and that all table names, column names, and values are spelled correctly.

Common Mistakes (Cont.)

  1. Not handling exceptions: Use try-except blocks to handle any exceptions that might occur during database operations.
  2. Ignoring error messages: Pay attention to error messages when things go wrong, as they can provide valuable information about what went wrong and how to fix it.
  3. Using raw SQL queries with user input: Be careful when using raw SQL queries with user input, as this can lead to SQL injection attacks. Use parameterized queries or prepared statements instead.
  4. Not escaping special characters in user input: When handling user input, make sure to escape any special characters that might interfere with your SQL queries.
  5. Ignoring performance considerations: Be mindful of the performance of your SQL queries and consider optimizing them if they become too slow or resource-intensive.

Practice Questions

  1. Write a Python script to create a new database and a table within it called "employees". The table should have columns for employee_id, first_name, last_name, department, and salary. Insert some sample data into the table.
  2. Modify the previous example to update an existing record in the customers table.
  3. Write a Python script to execute a SQL query that counts the number of records in the customers table.
  4. Create a Python function that accepts a department name as an argument and returns a list of all employees working in that department from the employees table.
  5. Write a Python script that retrieves data from the employees table, sorts it by salary in descending order, and limits the results to the top 10 highest-paid employees.
  6. Write a Python script that creates a new user with specific privileges for a given database.
  7. Write a Python script that drops a table from a specified database.
  8. Write a Python script that executes a SQL query that calculates the average salary of employees in each department and prints the results.
  9. Write a Python script that creates a backup of a specified database to a .sql file.
  10. Write a Python script that restores a database from a .sql file backup.

FAQ

  1. How can I handle errors when interacting with MySQL databases in Python?

Use try-except blocks to catch any exceptions that might occur during database operations.

  1. Can I use raw SQL queries in my Python scripts for better performance?

Yes, but be careful with user input to prevent SQL injection attacks. It's safer to use parameterized queries or prepared statements.

  1. How can I handle multiple results from a single query in Python?

Use the fetchmany() function to fetch multiple rows at once, or loop through the results using fetchone().

  1. What if my MySQL server is not on localhost?

Replace "localhost" with your MySQL server's IP address or domain name in the connection string.

  1. How can I secure my MySQL database from unauthorized access?

Use strong passwords, limit remote access, and consider using a firewall to block unwanted connections. Additionally, be mindful of SQL injection attacks and other security vulnerabilities when writing your queries.

MYSQL (Python Programming) | Python | XQA Learn