CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
user1919
user1919

Reputation: 3938

Creating concatenated query through psycopg2

I am using psycopg2 and Python in order to query a table in a PostGIS database. This is the query I build in order to exclude the column: the_geom

sqlstr = "SELECT 'SELECT ' || ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{table}' AND COLUMN_NAME NOT IN ('the_geom') ORDER BY ORDINAL_POSITION), ', ') || ' FROM {table}';".format(** {
    'table': name
})

cur.execute(sqlstr)
sqlstr = cur.fetchall()
print(sqlstr)

Which returns this:

SELECT fid, FS2011, NAME_1, Admin1, ANUAK, SIDAMO, BAGIRMI, BAKOMO, KOMA, KARAMOJO, SOUTHERN_L, DAGO, DINKA, ACHOLI, MURLE, LOTUKO, Area_m2, BARI_pc, SUDARAB_pc, SEREMUN_pc, AZANDE_pc, BANDA_pc, MORUMANGpc, NUER_pc, ANUAK_pc, TES_pc, SIDAM_pc, BERTA_pc, NORTH_L_pc, BAGRIMI_pc, BAKOMO_pc, KOMA_pc, KARAMOJOpc, SOUTJ_L_pc, DAGO_pc, DINKA_pc, ACHOLI_pc, MURLE_pc, LOTUK_pc, MAJORITY FROM ssd_ethnicitystatstics_adm1'

The issue is that the column names they are originally capital. For that reason I need to include quotes around each column name. For this I modified my query to:

sqlstr = "SELECT 'SELECT ' || ARRAY_TO_STRING(ARRAY(SELECT ('"' || COLUMN_NAME::VARCHAR(50) || '"') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{table}' AND COLUMN_NAME NOT IN ('the_geom') ORDER BY ORDINAL_POSITION), ', ') || ' FROM {table}';".format(** {
    'table': name
})

This will return an error of:

ProgrammingError: could not find array type for data type unknown

It must be related with quotation in Python and psycopg2.

Upvotes: 0

Views: 94

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

Use triple quotes:

sqlstr = """
    SELECT
        'SELECT ' ||
        ARRAY_TO_STRING(ARRAY(
            SELECT ('"' || COLUMN_NAME::VARCHAR(50) || '"')
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME='{table}' AND COLUMN_NAME NOT IN ('the_geom')
            ORDER BY ORDINAL_POSITION
        ), ', ') ||
        ' FROM {table}';
""".format(** {'table': name})

Upvotes: 1

Related Questions