Python encoding issue with pandas read_sql

Question

So I am trying to encode two strings to utf-8 so I can use them with pandas.read_sql:

selectedTable = "ACC__AccountCodes"
baseSql = "SELECT * FROM FileMaker_Fields WHERE TableName="

Now when I encode these two things:

baseSql.encode('utf-8')
selectedTable.encode('utf-8')
sqlString = "{}{}".format(baseSql, selectedTable)

My output looks like this:

b'SELECT * FROM FileMaker_Fields WHERE TableName='b'A\x00C\x00C\x00_\x00_\x00A\x00c\x00c\x00o\x00u\x00n\x00t\x00C\x00o\x00d\x00e\x00s\x00''

So when I run it with encoding set to 'latin1' I get the error:

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT * FROM FileMaker_Fields WHERE TableName=ACC__AccountCodes': ('HY000', '[HY000] [\x00F\x00i\x00l\x00e\x00M\x00a\x00k\x00e\x00r\x00]\x00[\x00F\x00i\x00l\x00e\x00M\x00a\x00k\x00e\x00r\x00]\x00 \x00F\x00Q\x00L\x000\x000\x000\x007\x00/\x00(\x001\x00:\x004\x007\x00)\x00:\x00 \x00T\x00h\x00e\x00 \x00c\x00o\x00l\x00u\x00m\x00n\x00 \x00n\x00a\x00m\x00e\x00d\x00 \x00"\x00A\x00"\x00 \x00d\x00o\x00e\x00s\x00 \x00n\x00o\x00t\x00 \x00e\x00x\x00i\x00s\x00t\x00.....

I can't seem to find anything addressing this. Everything I have tried leads me back to this which causes a sql error for invalid syntax. I am using pyodbc which expects utf-8 encoding as the input. Thoughts?

You shouldn't need explicit encoding. I recently set up pyodbc with FileMaker ODBC on Windows and the default Unicode encoding for pyodbc (which is UTF-16LE, BTW) worked fine. — Gord Thompson
– Gord Thompson, Commented Jul 3, 2018 at 23:57

nosklo · Accepted Answer · 2018-07-03 20:14:27Z

2

You don't have to encode them as utf-8; Try passing them as normal strings to pandas read_sql function, it should work fine, if not, then you have a problem somewhere else... but encoding is not what you want here.

Pyodbc accepts unicode strings in the query as normal, so that is not your problem at all.

I suggest also reading the Unicode section on pyodbc documentation that contains specific unicode configuration for some databases, although in your case I don't see that being a problem at all, because it is related to the database driver encoding and not your sql query, which should be a normal unicode string every time.

edited Jul 3, 2018 at 20:14

answered Jul 3, 2018 at 20:03

nosklo

224k58 gold badges300 silver badges299 bronze badges

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

4 Comments

L. Norman Over a year ago

Edited my question. I am using pyodbc which expects utf-8 encoding as the input.

nosklo Over a year ago

@L.Norman Your assumptions are wrong. I'm also using pyodbc and it works fine with normal unicode strings here. Show your code and the error you get when you use normal unicode strings, instead.

nosklo Over a year ago

Why are you setting the encoding in first place?

L. Norman Over a year ago

I believe my database output has utf characters in it. Edited my post

Collectives™ on Stack Overflow

Python encoding issue with pandas read_sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related