psycopg2.sql Inserting variable type using sql.Identifier results in type "integer" does not exist

Question

I'm trying to dynamically create tables using psycopg2.sql, I wrote a function that takes in a list of tuples consisting of the column name and the column's data type, then I create a string, call it query, using "{}" as placeholders like this:

create table {} ({} {}, {} {} ...);

I flatten out the list of tuples, so that the elements in the resulting list correspond to the order in which I want to insert them into the query string and use the resulting list as an argument in sql.SQL(query).format(...)

The resulting sql.Composed instance looks like this:

Composed([SQL('create table '), Identifier('games'), SQL(' ( '), Identifier('score_loser'), SQL(' '), Identifier('integer'), SQL(' , '), Identifier('playoffs'), SQL(' '), Identifier('boolean'), SQL(' , '), Identifier('record_loser'), SQL(' '), Identifier('integer[]'), SQL(' , '), Identifier('broadcast'), SQL(' '), Identifier('varchar(20)'), SQL(' , '), Identifier('date'), SQL(' '), Identifier('date'), SQL(' , '), Identifier('id'), SQL(' '), Identifier('varchar (30)'), SQL(' , '), Identifier('home_team'), SQL(' '), Identifier('varchar (50)'), SQL(' , '), Identifier('record_winner'), SQL(' '), Identifier('integer[]'), SQL(' , '), Identifier('winner'), SQL(' '), Identifier('varchar (50)'), SQL(' , '), Identifier('loser'), SQL(' '), Identifier('varchar (50)'), SQL(' , '), Identifier('score_winner'), SQL(' '), Identifier('integer'), SQL(' , primary key ('), Identifier('id'), SQL(') );')])

But when I try to execute this sql.Composed instance I get the error that "integer" is not a type, where "integer" was an element in the list I passed to the format function.

Is it not possible to also pass variable types dynamically using psycopg2.sql, or if it is could you please tell me how to do it?

Archirk · Accepted Answer · 2020-02-28 14:05:30Z

1

I had similar problem. I solved it with mixing string composition,format(), and list generators.

data = (('column_name_1','VARCHAR(2)'), ('column_name_2','INT'))
data = ['%s %s' % (x[0], x[1]) for x in data]
columns_string = ', '.join([x for x in data])

Here columns_string = 'column_name_1 VARCHAR(2), column_name_2 INT'

query = 'CREATE TABLE {}(%s)' % columns_string
query = sql.SQL(query).format(sql.Identifier('test_table'))

If we check query:

print(query.as_string(conn))

CREATE TABLE "test_table"(column_name_1 VARCHAR(2), column_name_2 INT)

We execute it like cur.execute(query) and it works. Not sophisticated and probably not reliable solution, but works.

answered Feb 28, 2020 at 14:05

Archirk

7023 gold badges14 silver badges31 bronze badges

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

Comments

SuaveSouris · Accepted Answer · 2021-02-10 18:20:11Z

I was also having so much trouble with this aspect. sql.Identifier is for double-quoted, well, SQL Identifiers which the datatypes (INTEGER, TEXT, etc.) are not. Looks like just making it plain SQL does the trick.

N.B. In your code, you should have pre-defined columns tuples and not expose their definition to the front-end. This is also why tuples are useful here as they are immutable.

import psycopg2.sql as sql

def create_table( name, columns ):
    # name = "mytable"
    # columns = (("col1", "TEXT"), ("col2", "INTEGER"), ...)
    fields = []
    for col in columns:
        fields.append( sql.SQL( "{} {}" ).format( sql.Identifier( col[0] ), sql.SQL( col[1] ) ) )

    query = sql.SQL( "CREATE TABLE {tbl_name} ( {fields} );" ).format(
        tbl_name = sql.Identifier( name ),
        fields = sql.SQL( ', ' ).join( fields )
    )
    print( query.as_string(conn) ) # CREATE TABLE "mytable" ( "col1" TEXT, "col2" INTEGER );
    # Get cursor and execute...

Collectives™ on Stack Overflow

psycopg2.sql Inserting variable type using sql.Identifier results in type "integer" does not exist

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related