psycopg_postgres_quickstart_create_table_insert_query.py

1import psycopg
2
3# Connect to an existing database
4with psycopg.connect("dbname=test user=postgres") as conn:
5
6    # Open a cursor to perform database operations
7    with conn.cursor() as cur:
8
9        # Execute a command: this creates a new table
10        cur.execute("""
11            CREATE TABLE test (
12                id serial PRIMARY KEY,
13                num integer,
14                data text)
15            """)
16
17        # Pass data to fill a query placeholders and let Psycopg perform
18        # the correct conversion (no SQL injections!)
19        cur.execute(
20            "INSERT INTO test (num, data) VALUES (%s, %s)",
21            (100, "abc'def"))
22
23        # Query the database and obtain data as Python objects.
24        cur.execute("SELECT * FROM test")
25        cur.fetchone()
26        # Will return the statement as: (1, 100, "abc'def")
27
28        # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list
29        # of several records, or even iterate on the cursor
30        for record in cur:
31            print(record)
32
33        # Make the changes to the database persistent
34        conn.commit()