How to use variables to insert into tables from python to PostgreSQL

Question

I am new to both python and SQL. What I want to do eventually is that user types a value to insert into a table in PostgreSQL. I have tried a few methods I could find,

but I have not been able to successfully insert. This is what I have now:

import psycopg2
from config import config

def create_tables(a):
    """ create tables in the PostgreSQL database"""
    commands = (
        """
        SET num 10,
        INSERT INTO Hotel_A (date, Suite, StandardKing) VALUES ('2017-09-12', num, 3)
        """,
        """
        INSERT INTO Hotel_A (date, Suite, StandardKing) VALUES ('2017-09-29', 5, 3)
        """,
        """
        INSERT INTO Hotel_A (date, Suite, StandardKing) VALUES ('2017-09-23', 5, 3)
        """
        )

    conn = None

    try:
        # read the connection parameters
        params = config()
        # connect to the PostgreSQL server
        conn = psycopg2.connect(**params)
        cur = conn.cursor()

        # create table one by one
        for command in commands:
            cur.execute(command)

        # close communication with the PostgreSQL database server
        cur.close()
        # commit the changes
        conn.commit()

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

    finally:
        if conn is not None:
            conn.close()

HotelASuite = 10

if __name__ == '__main__':
    create_tables(HotelASuite)

I want to do num = HotelASuite that is my goal.

Thank you so much for your help!. I am using Python 2.7 and PostgreSQL 9.6.

Include the code in the question, not a link to it. In other words produce a [mcve]. Have a look at for example [this Q/A](https://stackoverflow.com/questions/9075349/using-insert-with-a-postgresql-database-using-python) for how to use placeholders. And finally, never ever manually string format or concatenate values to queries. — Ilja Everilä, Aug 27 '17 at 04:58
so when I insert values like this, INSERT INTO Hotel_A (date, Suite, StandardKing) VALUES ('2017-09-12', 5, 3), instead of writing actual values, I want to use variables. so I want it to look like INSERT INTO Hotel_A (date, Suite, StandardKing) VALUES (date, int1, int2) where date, int1, and int2 are actually 2017-09-12, 5, and 3. Does this make sense? — Hirotaka Nakagame, Aug 27 '17 at 05:05
Yes. Have a look at the question linked in the previous comment. You're looking for placeholders. Also read the psycopg2 documentation on how to pass variables: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries. And once more, do not use any form of string formatting yourself. That includes f-strings, `str.format()`, and the old %-formatting. — Ilja Everilä, Aug 27 '17 at 05:45

score 2 · Accepted Answer · answered Aug 27 '17 at 14:11

2

 var1 = "x"
 var2 = datetime.datetime.now()
 curs.execute("INSERT INTO sometable (col1, col2) VALUES (%s, %s)", (var1,var2))

See the manual

answered Aug 27 '17 at 14:11

Craig Ringer

307,061
76
688
778

How to use variables to insert into tables from python to PostgreSQL

1 Answers1