How to Use PostgreSQL in Python Part 2

Connecting to a Database

To connect to your database, you should first create a connection object representing the database. Next, you should create a cursor object to help you in execution of your SQL statements.

The following example demonstrates how to establish a connection to the database named “postgres”:

import psycopg2
con = psycopg2.connect(database="postgres", 
                       user="postgres", 
                       password="", 
                       host="127.0.0.1", 
                       port="5432")
print("Database opened successfully")

The following is the list of parameters that have been passed to the connect() method:

• database: The name of the database you need to connect to.
• user: The username to be used for authentication.
• password: The database password for the user.
• host: The address of the database server. For example, a domain name, "localhost", or an IP address.
• port: The port number. If you don't provide this, the default one will be used, which is 5432.

Note that the values for the above parameters should be correct for the connection to be successful. If not, an exception will be generated. The output in the above code shows that the connection to the database has been established successfully.

Creating a Table

To create a Postgres table in Python, we use the CREATE TABLE SQL statement. This query should be executed after establishing a connection to the database. We also create a cursor object by calling the cursor() method that belongs to the connection object. This cursor object is used to actually execute your commands. We then call the execute() method of the cursor object to help us in creating the table. Finally, we need to commit and close the connection. "Committing" the connection tells the driver to send the commands to the database.

The following example demonstrates this:

import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute('''CREATE TABLE STUDENT
      (ADMISSION INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      COURSE        CHAR(50),
      DEPARTMENT        CHAR(50));''')
print("Table created successfully")
con.commit()
con.close()

Output

The commit() method helps us apply the changes that we have made to the database, and these changes cannot be undone if commit() runs successfully. The close() method will close the connection to the database.

At this point we have created a table with 4 columns, all of which have various data types. The above output shows that the table was created successfully.

Inserting Data

We can insert either a single record or multiple records into a Postgres database table. Again, we must first establish a connection to the database server by calling the connect() function. Next, we should create a cursor object by calling the cursor() method. Lastly, we should run the INSERT statement via the execute() method to add the data into the table.

Here is an example of this in action:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3420, 'John', 18, 'Computer Science', 'ICT')");

con.commit()
print("Record inserted successfully")
con.close()

Output

Since the commit() method isn't called until after we "execute" all of the INSERT statements, multiple records are inserted with a single call of the commit() method.

Retrieving Data

You can select data from a Postgres database and view the table records. First, you must establish a connection to the database using the connect() function. Next, a new cursor should be created by calling the cursor() method. The created cursor object can then be used to execute the SELECT statement for querying data from the database.

For example:

import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute("SELECT admission, name, age, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")
print("Operation done successfully")
con.close()

Output

Here we have retrieved data from the database by specifying the table and names of the columns that we need to retrieve from the database table. This data is returned to us as list of tuples, with the “top level” list being the rows of data. Then each row is a tuple of the column data. If no rows are returned for the query then an empty list is returned by fetchall().

Updating Tables

We can update or modify the details of a record that has already been inserted into a database table. First, we have to establish a connection to the database using the connect() method. Next, we call the cursor() function to create a cursor object. Finally, we run the execute() method to execute the UPDATE statement with input values.

For example:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("UPDATE STUDENT set AGE = 20 where ADMISSION = 3420")
con.commit()
print("Total updated rows:", cur.rowcount)

cur.execute("SELECT admission, age, name, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[2])
    print("DEPARTMENT =", row[3], "\n")

print("Operation done successfully")
con.close()

Output

In the above example, we have updated the value of column AGE for all rows where the ADMISSION is 3420. After executing the update we then retrieve this data to verify that the appropriate rows/columns have been updated.

Deleting Rows

To delete a record from a Postgres database table, we should first establish a connection to the database server. Secondly, a cursor object should be created by calling the cursor() function. We then run the DELETE statement to perform the deletion.

For example:

import psycopg2

con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")

cur = con.cursor()

cur.execute("DELETE from STUDENT where ADMISSION=3420;")
con.commit()
print("Total deleted rows:", cur.rowcount)

cur.execute("SELECT admission, name, age, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
    print("ADMISSION =", row[0])
    print("NAME =", row[1])
    print("AGE =", row[2])
    print("COURSE =", row[3])
    print("DEPARTMENT =", row[4], "\n")

print("Deletion successful")
con.close()

Output

In this example we have deleted all records where the admission number of the student is 3420, which in this case is only one row. After retrieving the data using SELECT, we can see that this record is not part of the above output, confirming that it has been deleted from the database.

Conclusion

With Python, there are various ways we can access a PostgreSQL database. There are many database drivers for Python that we can use for this purpose, but psycopg is the most popular one. In this article we showed how to install the module, establish a connection to your PostgreSQL database, and execute common SQL queries using Python code.