SQL Query in pandas python

Question

I am writing an SQL query in python pandas:

import pandas as pd
from pandas import DataFrame, read_csv
import numpy as np
from pandasql import sqldf
pysqldf=lambda q:sqldf(q,globals())
rolup = pysqldf(u"select MasterUserId,DeviceUsed,hcluster, count(MasterUserId) as Tot_Rec, sum(Visits),sum(PV),sum(TimeSpent) from clstrd_data group by MasterUserId,DeviceUsed,hcluster;")

Error:

sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

How to switch to Unicode strings? I am using python2.7.

new code: import pandas as pd from pandas import DataFrame, read_csv import numpy as np from pandasql import sqldf pysqldf=lambda q:sqldf(q,globals()) rolup = pysqldf(u"select MasterUserId,DeviceUsed,hcluster, count(MasterUserId) as Tot_Rec, sum(Visits),sum(PV),sum(TimeSpent) from clstrd_data group by MasterUserId,DeviceUsed,hcluster;") — Pulkit Jha
– Pulkit Jha, Commented Nov 19, 2014 at 9:38
your "new code" is identical to what's in the question - so maybe delete your confusing comment here? — WestCoastProjects
– WestCoastProjects, Commented Jul 22, 2018 at 13:50

hd1 · Accepted Answer · 2014-11-19 08:58:16Z

2

According to the python unicode howto:

In Python source code, Unicode literals are written as strings prefixed with the ‘u’ or ‘U’ character: u'abcdefghijk'

In other words, your script should read:

import pandas as pd
from pandas import DataFrame, read_csv
import numpy as np
from pandasql import sqldf
pysqldf=lambda q:sqldf(q,globals())
rolup = pysqldf(u"select MasterUserId,DeviceUsed,hcluster, count(MasterUserId) as Tot_Rec, sum(Visits),sum(PV),sum(TimeSpent) from clstrd_data group by MasterUserId,DeviceUsed,hcluster;")

Hope that helps.

answered Nov 19, 2014 at 8:58

hd1

34.9k5 gold badges83 silver badges95 bronze badges

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

2 Comments

Pulkit Jha Over a year ago

Thanks @hd1. I have changed my code but still getting exactly same error.

Pulkit Jha Over a year ago

Sir I have just pasted you code. Because only differece i could see was addition of u before select statement. Here is the new code: import pandas as pd from pandas import DataFrame, read_csv import numpy as np from pandasql import sqldf pysqldf=lambda q:sqldf(q,globals()) rolup = pysqldf(u"select MasterUserId,DeviceUsed,hcluster, count(MasterUserId) as Tot_Rec, sum(Visits),sum(PV),sum(TimeSpent) from clstrd_data group by MasterUserId,DeviceUsed,hcluster;")

Collectives™ on Stack Overflow

SQL Query in pandas python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related