psycopg2 adding quotes around my string that is creating problems

Question

I have the following script:

create_table_WAC = """
create table if not exists %s (
    w_geocode text,
    C000 text,
    CFS04 text,
    CFS05 text,
    createdate text
)
"""

target_directory = Path(sys.argv[1]).resolve()

for file in target_directory.rglob('*.csv'):

    table_name = 'opendata_uscensus_lodes_' + str(file.stem)
    print(table_name)

    # MAKE SURE THIS IS THE RIGHT TABLE FOR THE FILES
    cur.execute(create_table_WAC, (table_name,))

    with open(file,'r') as file_in:

        # MAKE SURE THIS HAS THE RIGHT TABLE NAME IN THE COPY STATEMENT
        cur.copy_expert("copy %s from stdin with csv header delimiter ','", table_name, file_in)

        conn.commit()

conn.close()

When I run it, it throws this error related to the CREATE TABLE command. I don't understand why there are '' added -- and how do I remove them?

Here is the error:

psycopg2.ProgrammingError: syntax error at or near "'opendata_uscensus_lodes_ca_wac_SA02_JT03_2003'"
LINE 2: create table if not exists 'opendata_uscensus_lodes_ca_wac_S...

Aside - reconsider building a whole new table for each CSV file. This is not ideal database design as you leave schema changes open to application and especially in a loop! Tables should fit in a relational model not as data dumps. DBAs will be tossing and turning at night with this setup. — Parfait
– Parfait, Commented Dec 14, 2018 at 22:40

klin · Accepted Answer · 2023-01-07 00:24:54Z

2

Use SQL string composition:

from psycopg2 import sql

create_table_WAC = """
create table if not exists {} ( -- note changed placeholder
    w_geocode text,
    C000 text,
    CFS04 text,
    CFS05 text,
    createdate text
)
"""

# ...

    cur.execute(sql.SQL(create_table_WAC).format(sql.Identifier(table_name)))

Read the comprehensive explanation in the documentation.

edited Jan 7, 2023 at 0:24

answered Dec 14, 2018 at 21:56

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

psycopg2 adding quotes around my string that is creating problems

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related