2

Connected to this question, but I'm missing something.

When I run the following 

table_name = 'my_schema.my_table'
cur.execute(sql.SQL('SELECT col1, col2 FROM {}').format(sql.Identifier(table_name)))

The query that is sent to the database is

SELECT col1, col2 FROM "myschema.myname"

And I get the error:

"relation "myschema.myname" does not exist"

I want the query to be 

SELECT col1, col2 FROM myschema.myname

When I pass this directly to cur.execute I don't have a problem.

If it is of help I connect to the database following this tutorial using a .ini file which in my case looks like: 

[postgresql]
host=ip_no_of_host
database=name_of_db
user=username
password=password
RoperMaps
  • 123
  • 7
  • Thanks for the suggestion. I don't connect to a specific schema just the database, also `myschema.mytable` works fine if I pass it directly to `cur.execute`. – RoperMaps Nov 09 '17 at 16:45
  • I've updated my question with conn details. removing the schema qualifier doesn't work - I still have the quotes around the table name. I don't think this is the issue, because the problem is removing the quote marks. As I said `cur.execute('SELECT col1, col2 FROM myschema.myname')` works fine. – RoperMaps Nov 09 '17 at 17:12

1 Answers1

2
schema_name = 'my_schema'
table_name = 'my_table'
cur.execute(sql.SQL('SELECT col1, col2 FROM {}.{}').format(
    sql.Identifier(schema_name), sql.Identifier(table_name)
    )
)

or just

table_name = 'my_table'
cur.execute(sql.SQL('SELECT col1, col2 FROM my_schema.{}').format(
    sql.Identifier(table_name)
    )
)
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Thank you, that solved it. Is this because `sql.Identifier(_str_)` objects map onto atomic postgres objects? – RoperMaps Nov 09 '17 at 20:47