Skip to content

Psycopg and Faker

Psycopg logo Faker logo

Connecting to a database with psycopg

psycopg implements the Python db-api interface for postgreSQL. Unlike the sqlite connection, which requires only a filename, to connect to a postgreSQL server you must supply a host, port, database name, username, and password. In the sample below:

  • The host is set to localhost, which works using the ssh tunnel you just created.
  • The default value for port is 5432, so it doesn't need to be specified unless you created a tunnel using a different port number.
  • The database name is sec1 or sec2, depending on your course section. The tables you create will automatically be created in your personal schema in your section's database (the schema's name is your username).
  • The username (your JMU username) and password (your student number) are included at the end.

Example

import psycopg

CONNSTR = "host=localhost port=5432 dbname=sec1 user=yourname password=yourpass"

def example_query():
    # Connect to postgres and create a cursor
    with psycopg.connect(CONNSTR) as conn:
        with conn.cursor() as cur:
            ...

The above example is not secure!

In general, you should avoid putting your username and password directly in source code. Here are two other ways to set your username and password:

  1. Set the environment variables PGUSER and PGPASSWORD in your operating system.

–OR–

  1. Create a ~/.pgpass file with lines in the format: hostname:port:database:username:password

See Environment Variables and The Password File in the PostgreSQL documentation for more details.

Create tables

Create a file named create_load.py and add the appropriate code using the example above as a guide. Write a function called create_tables that creates the same 2 tables that you created in HW2.

In the main block, call create_tables(). Use pgadmin to check that your tables exist in the database.

if __name__ == "__main__":
    create_tables()

Create fake data

Write 2 functions to create sensible fake data for the 2 tables. Make 20 rows of data in the main table. Each table should return the rows of data in a list of lists. Print the lists to check the data.

Insert the fake data

Write a function called insert_table1() that takes a list of lists as its argument and inserts the 20 rows into the primary table. Write insert_table2() to insert the generated data into the secondary table. Use pgadmin to be verify that the data was inserted.

Submission

Submit your file create_load.py via Gradescope.