CODEWITHSUNDEEP
pythonpython-3.xpandaspyodbc
L. Norman
L. Norman

Reputation: 483

Python encoding issue with pandas read_sql

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?

Upvotes: 2

Views: 6622

Answers (1)

nosklo
nosklo

Reputation: 223152

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.

Upvotes: 2

Related Questions