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?

Answer 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.

Answer 2

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...