python - How to convert syntaxt of query from mysql to sqlite3

Question

I have the following query executed using pandas' read_sql method that works great when querying against a MySQL db:

sql = "SELECT * FROM semantic_groups sg join semantic_types st on sg.tui = st.tui where group_name in ({})".format(', '.join(['%s' for _ in semtypes]))  

stypes = pd.read_sql(sql, params=[semtypes], con=engine)

However, when I run this against the same tables in a sqlite3 file, I get and error that:

Execution failed on sql 'SELECT * FROM semantic_groups sg join semantic_types st on sg.tui = st.tui where group_name in (%s)': near "%": syntax error

Good and fine, so I replace the %s with a ?, which, according to everything I've seen, should work, but I get the error:

Execution failed on sql 'SELECT st.tui, abbreviation, clamp_name, ctakes_name FROM semantic_groups sg join semantic_types st on sg.tui = st.tui where group_name in (?)': Error binding parameter 0 - probably unsupported type.

The parameter being passed, semtypes, is just a list of string values (['Anatomy'] for testing purposes), and group_name is a TEXT field, so I'm not sure what this error means?

question from:https://stackoverflow.com/questions/65839147/how-to-convert-syntaxt-of-query-from-mysql-to-sqlite3

Answer 1

Probably that should be params=semtypes?

sql = "SELECT * FROM semantic_groups sg join semantic_types st on sg.tui = st.tui where group_name in ({})".format(', '.join(['?' for _ in semtypes]))  

stypes = pd.read_sql(sql, params=semtypes, con=engine)