PostgreSQL

Getting Started

PostgreSQL is an actively developed and mature open source database. Using the psycopg2 module, we can execute queries on the database.

Installation using pip

pip install psycopg2

Basic usage

Lets assume we have a table my_table in the database my_database defined as follows.

|id|first_name|last_name |---|---|---|---|---|---|---|---|---|--- |1|John|Doe

We can use the psycopg2 module to run queries on the database in the following fashion.

import psycopg2

# Establish a connection to the existing database 'my_database' using
# the user 'my_user' with password 'my_password'
con = psycopg2.connect("host=localhost dbname=my_database user=my_user password=my_password")

# Create a cursor
cur = con.cursor()

# Insert a record into 'my_table'
cur.execute("INSERT INTO my_table(id, first_name, last_name) VALUES (2, 'Jane', 'Doe');")

# Commit the current transaction
con.commit()

# Retrieve all records from 'my_table'
cur.execute("SELECT * FROM my_table;")
results = cur.fetchall()

# Close the database connection
con.close()

# Print the results
print(results)

# OUTPUT: [(1, 'John', 'Doe'), (2, 'Jane', 'Doe')]