ADS
Python and PostgreSQL Integration: Step-by-Step Guide
In this article I will show you how to connect your python program to a PostgreSQL database.You will need a working python installation and PostgreSQL running in your machine. If you don't have python installed, please check python installation guide before moving to the next part.
installations
We will use psql the PostgreSQL command line tool to create our demo database in this tutorial , you can as well use the graphical interface, pgadmin, just make sure that your database is active and you have the necessary permissions.
run the following to check whether PostgreSQL is accessible through the command line
psql --version
if you get the version of PostgreSQL installed, congratulations you have PostgreSQL running in your system.
If you get an error like 'psql is not recognized ....', it means that you don't have PostgreSQL installed, head to postgresql.org and download the latest version.
Besides python and PostgreSQL you will need a python adapter for PostgreSQL, in our case we will use psycopg2 which is one of the best options.
run the following command on the command interface.
pip install psycopg2
Creating demo database
Now that we are done with installations, we will need to create a demo database, which we will use to connect to our python script.
createdb -U your_username demoYou will be prompted to enter the password for the user you have specified , Enter your password to complete creating the database. If no password error occurred , the database you have just created should be accessible both from psql or pgadmin. To check whether the database was created successfully run
psql -d demo -U your_usernameThe -d followed by demo tells psql that we want to open a database called demo while the -U followed by the username specifies which user we are going to open the database with.
Once again you will be prompted to enter your password, if successful your command line will open psql and point to the database you've just created .
Creating a connection class
We are now ready to connect our python code to the database we've just created , open a python script or create one and name it demo.py. You can use the command line to create one using the following command which will create a file named demo.py in your current directory.
type con > demo.pyOpen demo.py in your favorite text-editor and type the following lines of code and make sure to set user to your PostgreSQL username
import psycopg2 conn = psycopg2.connect( """dbname=demo user=your_username password=your@secret """)
In the first line we are importing psycopg2 and in the second line we are initializing a connection class.
dbname is used to specify the database that we want to start a connection with. Make sure that the password you specify is correct for the user pointed to by your username.
If you get no error after running the above code, the connection was successful and you are now ready to start making queries to your database.
Querying demo database.
To start making queries , you will need to create a cursor object, a direct way to do that is
cursor = conn.cursor() #using the conn object we created above to create our cursor
Let us see some queries in action
Creating a table
query = """ CREATE TABLE person ( name varchar(200), age int, country varchar(100) ) """ cursor.execute(query) conn.commit() cursor.close() conn.close()
The first line above is an SQL command for creating a person relation in our database, we specify that the table should contain three fields that is name , age , and the country the person comes from.
execute() method
In our code above we request for an SQL CREATE TABLE operation by calling cursor.execute(query).
The execute function returns None so to use the values returned by the executed query you will need to use retrieval methods such as fetchall we will look at data retrieval in a short while.
The execute method by itself does not do changes to the database so if you execute a query that will make changes to the database like in our case above you will need to call the commit() method of the connection instance.
You can avoid the overhead of calling conn.commit() every time you execute queries that are likely to alter the database by setting auto commit to True, default is False.
add the following line of code in your script and remove the conn.commit()
conn.autocommit = True
Your full script should now look like
import psycopg2 conn = psycopg2.connect( """dbname=demo user=your_username password=your@secret """) conn.automomplete=True cursor = conn.cursor() #using the conn object we created above to create our cursor query = """ CREATE TABLE person ( name varchar(200), age int, country varchar(100) ) """ cursor.execute(query) cursor.close() conn.close()
note the missing conn.commit() below the execute statement as it is unnecessary because auto commit is set to True.
The cursor class supports using it with the python's with keyword , this way you can use the cursor without needing to call cursor.close() because it is done for you by python in the background.
In your code remove the cursor = conn.cursor() and put all the cursor operations inside a with block like shown below
with conn.cursor() as cursor: query = """ CREATE TABLE person ( name varchar(200), age int, country varchar(100) )""" cursor.execute(query)
The with block is helpful where the developer might forget to terminate a process, for example the one shown above where you might forget to close the cursor thus leaving some of it's data suspended in memory.
Inserting data
To perform an SQL operations on bulk entries , the executemany method helps perform all the entries operations under one session.So if you ever need to execute bulky SQL operations you can use executemany , though the documentation says that performing execute method in a loop is a little bit faster than using executemany. The following code shows how we can use the executemany method to populate our person table.
#... with conn.cursor() as cursor: #.... people = (("Smith",24,"America"),("Eric",18,'Canada'),("Leisel",14,"Germany"),("John",20,"Kenya")) cursor.executemany("iNSERT INTO person (name,age,country) VALUES (%s, %s, %s)",people)
Make sure to remove the query for creating table person , otherwise you will get a duplicate error since the table is already created.
If you run the above script your database will be populated with the data we specified in the people tuple, let's prove that. Open the command line and move to demo database by running,
psql -d demo -U usernameEnter your password and when the name of the database appears , run the following SQL command to get all the entries in table person.
SELECT * FROM person;
The above image shows that our execute many function worked successfully.
data retrieval
To use data from a PostgreSQL database in our python program , we will need to use a SELECT query to get data and the fetch methods of our cursor class to turn the data into their equivalent python data types.
For example if we want to get people present in the person table of the demo database we will do it like:
#This assumes you have already initiated the connection class and set autocommit to True with conn.cursor() as cursor: cursor.execute(" SELECT * FROM demo") person1 = cursor.fetchone() print(person1) people = cursor.fetchall() print(people)
If you call the retrieval methods such as fetchone , fetchmany or fetchall and the leading execute command did not return any data , a ProgrammingError will be raised.
The fetchone method returns the first item returned by the SELECT query while fetchall as name suggests returns a tuple of all the elements.
You can as well use fetchmany which takes the number of elements you want as it's argument.
You learned how to integrate Python with PostgreSQL, hope you found the article helpful .