sqlalchemy.exc.ArgumentError: columns argument to select() must be a Python list or other iterable

user_13080433 0
Viewed 2306

New to Python and SQLquery. I am trying to query an existing table in mssql. First task is displaying what is in the table, the other tasK i also want to do is do an exists() statment to check if the data in the table does exist. These are me code snippets so far:

def dbhandler():
    params = urllib.parse.quote_plus(db_string)
    engine = db.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))

    connection = engine.connect()
    metadata = db.MetaData()
    odfs_tester_history_files = db.Table('odfs_tester_history_files', metadata, autoload=True, autoload_with=engine)
    odfs_tester_history = db.Table('odfs_tester_history', metadata, autoload=True, autoload_with=engine)
    tables_dict = {'odfs_tester_history_files': {'engine': engine, 'connection': connection, 'table': odfs_tester_history_files}, 'odfs_tester_history': {'engine': engine, 'connection': connection, 'table': odfs_tester_history}}
    #return {'engine': engine, 'connection': connection, 'table': odfs_tester_history_files}
    return tables_dict

db_instance = dbhandler()
odfs_tabletest_dict = db_instance['odfs_tester_history']
foo_col = db.sql.column('CSV_FILENAME')
sql = db.select(odfs_tabletest_dict['table']).where(odfs_tabletest_dict['odfs_tester_history'].c.foo_col == '06_16_2020_FMGN519.csv')
df = pd.read_sql(sql, odfs_tabletest_dict['connection'])
print(df)

However when I run the code, I get the following error: sqlalchemy.exc.ArgumentError: columns argument to select() must be a Python list or other iterable. How can I fix this? Also how can i use an exists statement to check if the data in question, the .csv file is in the table and just return a true or false value for me to use as duplicate check?

sqlalchemy python-3.x sql-server python
3 Answers

In Case anyone runs into this issue, I fixed it by putting square brackets around my table object. I have no idea why this works and neither does my mentor. He had similar code and it worked for him without the need for a [ ].

sql = db.select([odfs_tabletest_dict['table']]).where(odfs_tabletest_dict['table'].c.CSV_FILENAME == '06_16_2020_FMGN519.csv')
df = pd.read_sql(sql, odfs_tabletest_dict['connection'])
user_130804330

Gord Thompsons comment on this helped me to fix this. I upgraded to SQLAlchemy 1.4.11 and got rid of this issue.

user_101261250

If anyone is still facing this issue, as a previous answer mentioned, be aware of the version,

I am on version '1.3.10', and what you need is to pass model.columns to the select()

model = db.Table('my_table_name', metadata, autoload=True, autoload_with=engine)
query = db.select(model.columns)
user_33958140
Related