pandas.io sql.execute() variable-unicode-query-param with IN operator

Question

from pandas.io import sql

print sql.execute('SELECT * FROM testTable WHERE testColumn IN ("테스트1", "테스트2")', engine).fetchall()

When I try the test code above, it's works like a charm.

But because of the reason that the query should work dynamically, somehow I did think about the code below(But not works).

I think the problem related with escape character of unicode, but I have no idea how could I solve this problem despite of searching & trying many cases in stackoverflow.

testss = [u"테스트1", u"테스트2"]
print sql.execute('SELECT * FROM testTable WHERE testColumn IN {}'.format(tuple(tests)), engine).fetchall()

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "'\uc774\ud130\ub110 \uc120\uc0e4\uc778'": syntax error [SQL: "SELECT * FROM boxOffice WHERE movieNm IN (u'\\ud14c\\uc2a4\\ud2b81', u'\\ud14c\\uc2a4\\ud2b82')"]

If possible, please let me know how could it be applicable to read_sql_query, or read_sql related with sqlalchemy.text().

Any advice would be most welcome! Thank you for your help.

cf. http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries

su79eu7k · Accepted Answer · 2015-12-28 14:18:53Z

1

print sql.execute('SELECT * FROM testTable WHERE testColumn IN ("{}")'.format(u'", "'.join(tests).encode('utf-8')), engine).fetchall()
print pd.read_sql_query('SELECT * FROM testTable WHERE testColumn IN ("{}")'.format(u'", "'.join(tests).encode('utf-8')), engine)

Someone posted very close to the correct answer but deleted soon. Anyway I tweaked that code and both above worked fine.

answered Dec 28, 2015 at 14:18

su79eu7k

7,3664 gold badges36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas.io sql.execute() variable-unicode-query-param with IN operator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related