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 minimal reproducible example. Have a look at for example this Q/A for how to use placeholders. And finally, never ever manually string format or concatenate values to queries. — Ilja Everilä
– Ilja Everilä, Commented Aug 27, 2017 at 4: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
– Hirotaka Nakagame, Commented Aug 27, 2017 at 5: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: initd.org/psycopg/docs/…. And once more, do not use any form of string formatting yourself. That includes f-strings, str.format(), and the old %-formatting. — Ilja Everilä
– Ilja Everilä, Commented Aug 27, 2017 at 5:45

Craig Ringer · Accepted Answer · 2017-08-27 14:11:21Z

3

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

See the manual

answered Aug 27, 2017 at 14:11

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related