0

I know I can do it manually using sqlalchemy and pandas 

dbschema ='myschema'
engine = create_engine('postgresql://XX:YY@localhost:5432/DB', 
                       connect_args={'options': '-csearch_path={}'.format(dbschema )})

df =  psql.read_sql('Select * from myschema."df"', con = engine)

But is it possible to do a loop and to get all the tables ?

I tried something like 

tables = engine.table_names()

print(tables) 
['A', 'B']

for table in tables :
table =  psql.read_sql('Select * from myschema."%(name)s"', con = engine, params={'name' : table})

I get this message:

LINE 1: Select * from myschema.'A'

I guess the problem is caused by my quotes but I am not so sure.

EDIT : So I tried the example here : Passing table name as a parameter in psycopg2

from psycopg2 import sql

try:
    conn = psycopg2.connect("dbname='DB' user='XX' host='localhost' password='YY'")
except:
    print ('I am unable to connect to the database')
print(conn)
cur = conn.cursor()
for table in tables :
    table =  cur.execute(sql.SQL("Select * from myschema.{}").format(sql.Identifier(table)))

But my tables are 'None' so I am doing something wrong but I can't see what.

Victor A
  • 55
  • 5
  • have you checked without the quotes? just in case. – svemaraju Mar 13 '19 at 09:40
  • If I use 'Select * from myschema.%(name)s' I get the same error – Victor A Mar 13 '19 at 09:52
  • Identifiers (e.g. table names, column names) cannot be passed in for substitution via `params`. You'll have to use string interpolation for those parts of the query or, since you have a PostgreSQL database, fiddle around with the nice SQL string composing helpers in `psycopg2`'s [`sql`](http://initd.org/psycopg/docs/sql.html) module, specifically the [`Identifier`](http://initd.org/psycopg/docs/sql.html#psycopg2.sql.Identifier) class in this case. – shmee Mar 13 '19 at 10:03
  • Possible duplicate of [Passing table name as a parameter in psycopg2](https://stackoverflow.com/questions/13793399/passing-table-name-as-a-parameter-in-psycopg2) – shmee Mar 13 '19 at 10:06
  • I get the idea but I don't know how to use it. The answer doesn't explain how to use a list of names, or if it does I don't understand how. – Victor A Mar 13 '19 at 12:20
  • @VictorA What do you mean by `my tables are 'None'`? The `tables` variable from the `for` loop? It is not clear/visible where you take that from in the snippet from your edit. There's also some more inconsistencies in the code you show here. In the first snippet you declare `dbschema ='myschema'` but then pass a _variable_ named `myschema` to the `format` on `-csearch_path={}`. In the second snippet you fetch `tables` from `engine`, but pass `engine_meteo` to `read_sql` on what I assume is `pandas`, which you have mentioned nowhere, btw. Could you provide a clean, working example, please? – shmee Mar 13 '19 at 13:36

0 Answers0