Question about creating table using psycopg2's sql module

Question

'''

host = "localhost"
user = "postgres"
password = "Lall1739!@#"
port = "5432"
dbname = "snp500"
con_form = "host={0} user={1} password={2} port={3} dbname={4}".format(host, user, password, port, dbname)

con = psycopg2.connect(con_form)
cur = con.cursor()

components_name, components_prices, components_fetch_dates = fetch_investing_snp500_components_datas()

for component_name in components_name:
    table_name = component_name
    cur.execute(
        query=sql.SQL("CREATE TABLE %s"), 
        vars=(sql.Identifier(table_name))
    )

'''

TypeError: 'Identifier' object does not support indexing

It's also not a good idea to use Python string interpolation to build the query string.

So I am trying to write a query using the sql module.
What exactly are the benefits of writing a query using the sql module and why am I getting a Type error?

The benefits and proper usage are spelled out in the docs sql — Adrian Klaver
– Adrian Klaver, Commented Jan 1, 2022 at 16:10

SebDieBln · Accepted Answer · 2022-01-01 15:42:54Z

2

Note that execute is used for parameter substitution (.i.e. string literals), whereas you want to substitute identifiers (i.e. table names). Use SQL.format() for that:

cur.execute(
    query=sql.SQL("CREATE TABLE {table}").format(
        table=sql.Identifier(table_name),
    ), 
)

answered Jan 1, 2022 at 15:42

SebDieBln

3,4891 gold badge12 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Question about creating table using psycopg2's sql module

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related