Python Language Database Access PostgreSQL Database access using psycopg2
Example
psycopg2 is the most popular PostgreSQL database adapter that is both lightweight and efficient. It is the current implementation of the PostgreSQL adapter.
Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety (several threads can share the same connection)
Establishing a connection to the database and creating a table
import psycopg2
# Establish a connection to the database.
# Replace parameter values with database credentials.
conn = psycopg2.connect(database="testpython",
user="postgres",
host="localhost",
password="abc123",
port="5432")
# Create a cursor. The cursor allows you to execute database queries.
cur = conn.cursor()
# Create a table. Initialise the table name, the column names and data type.
cur.execute("""CREATE TABLE FRUITS (
id INT ,
fruit_name TEXT,
color TEXT,
price REAL
)""")
conn.commit()
conn.close()
Inserting data into the table:
# After creating the table as shown above, insert values into it.
cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
VALUES (1, 'Apples', 'green', 1.00)""")
cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
VALUES (1, 'Bananas', 'yellow', 0.80)""")
Retrieving table data:
# Set up a query and execute it
cur.execute("""SELECT id, fruit_name, color, price
FROM fruits""")
# Fetch the data
rows = cur.fetchall()
# Do stuff with the data
for row in rows:
print "ID = {} ".format(row[0])
print "FRUIT NAME = {}".format(row[1])
print("COLOR = {}".format(row[2]))
print("PRICE = {}".format(row[3]))
The output of the above would be:
ID = 1
NAME = Apples
COLOR = green
PRICE = 1.0
ID = 2
NAME = Bananas
COLOR = yellow
PRICE = 0.8
And so, there you go, you now know half of all you need to know about psycopg2! :)
