Psycopg and Faker

In this lab you will write create_load.py, a program that builds your HW2 tables in PostgreSQL and fills them with realistic fake data. You need the hw2_pg.sql file from Tuesday's activity. The program has the same shape as the faker_demo_hotels.py example from class, and you should keep that file open as a guide.

Step 1: Activate your venv

You already have a virtual environment with psycopg and faker in it: the one you created for the class repository. Make a folder for today's lab outside of your planner clone, so that you do not commit your lab files to the class repository. Then activate the planner environment from wherever you are:

source path/to/planner/.venv/bin/activate        # Linux and macOS
source path/to/planner/.venv/Scripts/activate    # Windows (Git Bash)

Check that the right packages are there:

pip show psycopg faker

In VS Code, open the lab folder, run Python: Select Interpreter from the command palette (F1), and choose Enter interpreter path… to pick the python inside planner/.venv.

Step 2: Store your password

In general, you should never put a password in source code. Instead, PostgreSQL clients look for a password file in your home directory. Create the file and add these two lines, replacing username and password with your e-ID and student number:

localhost:5432:*:username:password
data.cs.jmu.edu:5432:*:username:password

Each line is hostname:port:database:username:password, and * matches any database.

On Linux and macOS, the file is ~/.pgpass. It must be readable only by you, or PostgreSQL ignores it (with a warning):

chmod 600 ~/.pgpass

On Windows, the file is %APPDATA%\postgresql\pgpass.conf. From Git Bash:

mkdir -p "$APPDATA/postgresql"
code "$APPDATA/postgresql/pgpass.conf"

See The Password File in the PostgreSQL documentation for details.

Step 3: Connect with psycopg

psycopg implements the Python DB-API for PostgreSQL, so most of what you did with sqlite3 in HW2 carries over. Unlike SQLite, which needs only a filename, a PostgreSQL connection needs a host, database name, and username. The port defaults to 5432, and the password comes from your password file.

Start ssh stu (unless you are on campus and use host=data.cs.jmu.edu), then create create_load.py with:

import psycopg

CONNSTR = "host=localhost dbname=sec1 user=username"

if __name__ == "__main__":
    with psycopg.connect(CONNSTR) as conn, conn.cursor() as cur:
        cur.execute("SELECT current_user, current_schema")
        print(cur.fetchone())

You should see your e-ID twice: your username, and the schema where your tables go.

Differences from sqlite3

	`sqlite3`	`psycopg`
Placeholder	`?`	`%s` (for every data type, not just strings)
Leaving `with conn:`	commits, but leaves the connection open	commits (or rolls back on an error) and closes
Foreign keys	need `PRAGMA foreign_keys = ON`	always on
Errors	`sqlite3.IntegrityError`	`psycopg.errors.ForeignKeyViolation`, `UniqueViolation`, `CheckViolation`, …

Step 4: Create the tables

Write a function create_tables() that runs the DROP and CREATE TABLE statements from your hw2_pg.sql. Call it from the main block, run the program, and check in pgAdmin that the tables exist in your schema. (Right-click Tables and choose Refresh.) Running the program again should drop and recreate the tables without errors.

Chapter 8: Data Types in the PostgreSQL 18 documentation.

Step 5: Generate the 1st table

Write make_table1(count), replacing table1 with your table's actual name. It should return a list of count tuples, one per row, without the id column. Use a Faker provider that fits each column, so the data looks like the real thing:

A person's name is fake.name(), not fake.word().
A price is fake.pydecimal(left_digits=3, right_digits=2, positive=True), not any float.
A date is fake.date_between(start_date="-2y", end_date="today"), not a string.
A small set of fixed values is random.choice([...]) or fake.random_element([...]).

Every value must pass your NOT NULL and CHECK constraints. If PostgreSQL rejects a row, decide whether the generator or the constraint is wrong.

Then write insert_table1(rows), which inserts the rows and returns the list of ids that PostgreSQL generated. Add RETURNING id to the INSERT statement, and call cur.fetchone() after each execute(). Generate and insert 20 rows.

Step 6: Generate the 2nd table

Write make_table2(ids), which takes the ids from Step 5 and returns rows for the second table. Each foreign key value must be one of those ids, so use random.choice(ids), or loop over the ids the way the demo does with hotel phones. Make the number of rows per parent vary: some parents should have several, and at least one should have none.

Write insert_table2(rows) using cur.executemany(), since this table's generated ids aren't needed.

Finally, add these two lines near the top of your program, so that the fake data is the same every time you run it:

Faker.seed(374)
random.seed(374)

Step 7: Check your data

In pgAdmin, write a query that joins your two tables and counts the rows in the second table for each row in the first.

Does every count look plausible?
Which rows of the first table are missing from the result, and why? (We will fix that with outer joins after the exam.)
Would someone reading your data believe that it came from a real system?

Submission

Submit your file create_load.py via Gradescope. Make sure your password does not appear anywhere in the code.