Python Script to Run SQL Query

Python is a powerful language that allows you to even connect to databases and run SQL queries. Often you may need to fetch data from a database from within your python application or website. Python provides many useful libraries for this purpose. In this article, we will look at how to connect to database using python and also create a python script to run SQL query against your database.

Python Script to Run SQL Query

Here are the steps to run SQL query in python.

1. Install Python database library

First you need to install the right python library, depending on the type of your database, to query your database. If you use MySQL database, install MySQLdb package. If you use PostgreSQL, install psycopg2 or pygresql.

Open terminal and run the following commands, depending on your database type.

MySQL

pip install MySQL-python
pip install MySQL-python-connector

PostgreSQL

pip install psycopg2
pip install pygresql

2. Create Python Script

Open terminal and run the following command to create an empty python script.

$ sudo vi db_query.py

Add the following line to it, to set execution environment.

#!/usr/bin/env python

3. Import Library

Depending on your Database, add the following line to import the appropriate library to your python script.

MySQL

import MySQLdb as db_connect

PostgreSQL

import psycopg2 as db_connect

4. Connect to your database

Next, add the following lines to connect to your database. Replace the values of host_name, db_user, db_password, and db_name with your database’s host address, username, password and database name respectively. We use the connect() method to establish database connection, and pass the database user credentials as arguments.

host_name="localhost"
db_user="test_user"
db_password="123"
db_name="project

connection = db_connect.connect(host=host_name,user=db_user,password=db_password,database=db_name)
 
cursor = connection.cursor()

The above lines will connect to your database and return a connection object that can be used to connect to database. It also provides a cursor object used to send queries, fetch result and traverse it.

5. Query database

Next, write your SQL query.

query = "select * from data limit 5"

Next, we use the cursor object to execute the query, using execute command.

cursor.execute(query)
results = cursor.fetchall()
print(result)

The above command will run your SQL query and fetch its result. We use fetchall() function to get all rows of data. You can even use fetchone() to get one row at a time. You can print the result using print command, or loop through it and do further data manipulation.

6. Close connection

Finally, when you are done, it is important to close the connection so that it doesn’t keep your database database server busy.

connection.close()

Save and close the file. Here is the entire code for your reference.

#!/usr/bin/env python

import MySQLdb as db_connect

host_name="localhost"
db_user="test_user"
db_password="123"
db_name="project

connection = db_connect.connect(host=host_name,user=db_user,password=db_password,database=db_name)
 
cursor = connection.cursor()

query = "select * from data limit 5"

results = cursor.execute(query).fetchall()
print(result)

connection.close()

Make the file executable with the following command.

$ sudo chmod +x db_query.py

You can run the script with the following command.

$ sudo python db_query.py

That’s it. In this article, we have learnt how to connect to database and run SQL queries against them. You may modify it as per your requirement. The key is to create a connection object using connect() function, use the cursor generated using cursor() function to run queries and fetch result. And finally close the database connection.

It is important to note that almost all database libraries use the same functions of connect(), cursor(), execute(), fetchall() to work with databases. So you can use the above code to query almost any relational database, just by changing the import statement at the beginning of .py file.

Python Script to Run SQL Query