How to Connect to an SQL Database in Python with Example Code

Python is a versatile programming language that can be used to connect to and interact with various SQL databases. In this article, we will explore how to connect to an SQL database and run a simple query using Python. We will use the mysql-connector-python library to connect to a MySQL database, but the process is similar for other SQL databases like SQLite and MySQL.

Requirements

To follow along, you will need to have Python installed on your machine. Additionally, you will need to install the appropriate library for the SQL database you wish to connect to. For this example, we will use mysql-connector-python.

Step 1: Install the Required Library

First, install the Python library for MySQL using the following command:

pip install mysql-connector-python

Step 2: Connect to the Database

Now, let's write a Python script to connect to a MySQL database and perform some basic operations. Replace the placeholders with your actual database credentials:

import sql_host  '127.0.0.1'sql_user  'temp'sql_password  'temp'sql_database  'temp'# Connect to the MySQL databasecon  (    hostsql_host,    usersql_user,    passwordsql_password,    databasesql_database)# Create a cursor object to interact with the databasecursor  ()# Create a table if it doesn't existcursor.execute('''    CREATE TABLE IF NOT EXISTS users (        id INT AUTO_INCREMENT PRIMARY KEY,        name VARCHAR(255),        age INT    )''')# Insert some sample data into the tablecursor.executemany('''    INSERT INTO users (name, age) VALUES (%s, %s)''', [    ('F1', 'L1', 30),    ('F2', 'L2', 28),    ('F3', 'L3', 35)])# Commit the changes to the database()# Execute a SELECT query with filtering and orderingcursor.execute('''    SELECT * FROM users WHERE age  25 ORDER BY age DESC''')# Fetch a specific number of rows at a timebatch_size  5rows  cursor.fetchmany(batch_size)# Print the filtered and ordered resultsprint(rows)while rows:    for row in rows:        print(row)    rows  cursor.fetchmany(batch_size)# Close the cursor and connection()()

Explanation

The script first connects to the MySQL database using the provided credentials. It then creates a table named users if it does not exist, and inserts some sample data into this table. After that, it executes a SELECT query to fetch users whose age is greater than 25, ordered by age in descending order. The query results are fetched in batches, and each batch is printed to the console. Finally, the cursor and connection are closed to release resources.

Conclusion

This example demonstrates a simple way to connect to an SQL database using Python and execute basic SQL queries. For more detailed examples and scenarios, you can refer to the following GitHub repository:

GitHub - techbeamers/python-sql-examples: How to Connect Python to SQL Database with Examples

Feel free to explore and modify the code to better suit your needs.