CSV to sqlite3 database.Convert list from Utf8 to unicode

Question

import csv, sqlite3

conn = sqlite3.connect("mycustomers9.sql")
curs =  conn.cursor()
try:
    curs.execute("CREATE TABLE t (unknown1 TEXT, county TEXT, businessName TEXT, address1 TEXT, city1 TEXT, zip1 INTEGER, phone1 INTEGER,Email1 TEXT, approvalstatus TEXT, date1 TEXT, date2 TEXT, typeofConstruct TEXT, typeofBiz TEXT, unknown2 TEXT, unknown3 TEXT, unknown4 TEXT, unknown5 TEXT, unknown6 TEXT,BizName2 TEXT,Address2 TEXT, City2 TEXT,Zip2 TEXT,Country2 TEXT,Phone2 TEXT,Email2 TEXT,Phone3 TEXT);")
except sqlite3.OperationalError:
    print "Table already exist"
with open('HR_plan_review.csv', 'rb') as infile:
    dr = csv.DictReader(infile, delimiter = ',')
    to_db = [(i["unknown1"], i['county'], i['businessName'], i['address1'], i['city1'], i['zip1'], i['phone1'], i['Email1'], i['approvalstatus'], i['date1'],i['date2'], i['typeofConstruct'], i['typeofBiz'], i['unknown2'], i['unknown3'], i['unknown4'], i['unknown5'], i['unknown6'], i['BizName2'], i['Address2'], i['City2'], i['Zip2'], i['Country2'], i['Phone2'], i['Email2'], i['Phone3']) for i in dr]

curs.executemany("INSERT INTO t (unknown1, county, businessName, address1, city1,zip1, phone1, Email1, approvalstatus, date1, date2,typeofConstruct, typeofBiz, unknown2, unknown3, unknown4,unknown5, unknown6,BizName2,Address2, City2,Zip2,Country2,Phone2,Email2,Phone3) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", to_db)

to_db returns a list which is encoded in utf-8 and sqllite database seem to be requesting the formating to be in unicode. How can i convert "to_db" list to unicode before running the sql statement above. Below is the error message i get when i run the above code.

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

Edited based on input from answers

The revised code(below) executes successfully now but it does not insert the values taken from csv to the database.

import csv, sqlite3

conn = sqlite3.connect("mycustomers12.sql")
curs =  conn.cursor()
try:
    curs.execute(""" CREATE TABLE t (unknown1 TEXT, county TEXT, businessName TEXT, address1 TEXT, city1 TEXT, zip1 INTEGER, \n
    phone1 INTEGER,Email1 TEXT, approvalstatus TEXT, date1 TEXT, date2 TEXT, typeofConstruct TEXT, typeofBiz TEXT, unknown2 TEXT, \n
    unknown3 TEXT, unknown4 TEXT, unknown5 TEXT, unknown6 TEXT,BizName2 TEXT,Address2 TEXT, City2 TEXT,Zip2 TEXT,Country2 TEXT,\n
    Phone2 TEXT,Email2 TEXT,Phone3 TEXT);""")
except sqlite3.OperationalError:
    print "Table already exist"


infile = open('HR_plan_review.csv', 'rb') 
dr = csv.DictReader(infile, delimiter = ',')
keys=("unknown1", 'county', 'businessName', 'address1',
    'city1', 'zip1', 'phone1', 'Email1', 'approvalstatus',
    'date1','date2', 'typeofConstruct', 'typeofBiz', 'unknown2',
    'unknown3', 'unknown4', 'unknown5', 'unknown6', 'BizName2',
    'Address2', 'City2', 'Zip2', 'Country2', 'Phone2',
    'Email2', 'Phone3')
args=[tuple(key.decode('utf-8') for key in keys) for row in dr]
sql='INSERT INTO t ({f}) VALUES ({p})'.format(
    f=','.join(keys),
    p=','.join(['?']*len(keys)))
curs.executemany(sql, args)

What does it mean, 'does not insert the values' ? Do you get an error ? We cannot reproduce your example because we don't have your file - pasting at least a few lines from that would be helpful for people trying to help you. Random guess - you are not properly closing the connection, so maybe the .sql file doesn't actually get written properly ? — Thomas Vander Stichele
– Thomas Vander Stichele, Commented Oct 28, 2011 at 9:27
When i run this above code i expect it to create the sqlite database; insert the values retrieved from csv file i provided which is "mycustomers12.sql" to appropriate columns. It does create the database, but values which are in the form of comma seperated values are not inserted in the code. There is no errors it executes but without any errors but fails to insert the values. — shaytac
– shaytac, Commented Oct 28, 2011 at 14:45

cschorn · Accepted Answer · 2011-10-27 17:28:50Z

1

Sadly, csv cannot handle unicode (at least in Python 2.7). But you can work around the issue by wrapping the DictReader in a generator:

with open('HR_plan_review.csv', 'rb') as infile:
    dr = csv.DictReader(infile, delimiter = ',')
    def unicoded_data():
        for row in dr:
            # Assuming infile encoding is utf-8.
            yield dict([(key, unicode(value, encoding='utf-8'))
                       for key, value in row.iteritems()])

    to_db = [(i["unknown1"], i['county'], i['businessName'], i['address1'], i['city1'], i['zip1'], i['phone1'], i['Email1'], i['approvalstatus'], i['date1'],i['date2'], i['typeofConstruct'], i['typeofBiz'], i['unknown2'], i['unknown3'], i['unknown4'], i['unknown5'], i['unknown6'], i['BizName2'], i['Address2'], i['City2'], i['Zip2'], i['Country2'], i['Phone2'], i['Email2'], i['Phone3']) for i in unicoded_data()]

edited Oct 27, 2011 at 17:28

answered Oct 27, 2011 at 17:22

cschorn

1,21711 silver badges11 bronze badges

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

Comments

unutbu · Accepted Answer · 2011-10-27 18:27:46Z

0

keys=("unknown1", 'county', 'businessName', 'address1',
    'city1', 'zip1', 'phone1', 'Email1', 'approvalstatus',
    'date1','date2', 'typeofConstruct', 'typeofBiz', 'unknown2',
    'unknown3', 'unknown4', 'unknown5', 'unknown6', 'BizName2',
    'Address2', 'City2', 'Zip2', 'Country2', 'Phone2',
    'Email2', 'Phone3')
args=[tuple(i[key].decode('utf-8') for key in keys) for row in dr]
sql='INSERT INTO t ({f}) VALUES ({p})'.format(
    f=','.join(keys),
    p=','.join(['?']*len(keys)))
curs.executemany(sql, args)

Or, for a more robust solution, you could use UnicodeDictReader, slightly modified version of UnicodeReader (from the csv docs) to return rows as dicts with unicode values:

class UTF8Recoder:
    """
    Iterator that reads an encoded stream and reencodes the input to UTF-8
    """
    def __init__(self, f, encoding):
        self.reader = codecs.getreader(encoding)(f)

    def __iter__(self):
        return self

    def next(self):
        return self.reader.next().encode("utf-8")

class UnicodeDictReader:
    def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
        f = UTF8Recoder(f, encoding)
        self.reader = csv.DictReader(f, dialect=dialect, **kwds)

    def next(self):
        row = self.reader.next()
        return dict((key,unicode(val, "utf-8")) for key,val in row.iteritems())

    def __iter__(self):
        return self

with open('HR_plan_review.csv', 'rb') as infile:
    dr = UnicodeDictReader(infile, delimiter = ',')

The code I posted above could still be used, just change

args=[tuple(i[key].decode('utf-8') for key in keys) for row in dr]

to

args=[tuple(i[key] for key in keys) for row in dr]

edited Oct 27, 2011 at 18:27

answered Oct 27, 2011 at 17:24

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

3 Comments

shaytac Over a year ago

Even though csv module is already imported. I get "AttributeError: 'module' object has no attribute 'UnicodeDictReader'

unutbu Over a year ago

Oops, I should not have written csv.UnicodeDictReader, since I'm defining UnicodeDictReader in the script itself. Change it to UnicodeDictReader (without the csv.).

shaytac Over a year ago

Here is the revised code and output error i m getting. First part is modified code, and at the bottom is the output error. pastebin.com/pzsznp1U

dougalg · Accepted Answer · 2013-03-31 02:33:16Z

You need to commit the execute:

conn.commit()

also your executemany statement should be placed inside the "with" block:

import csv, sqlite3

myfile = 'CSV FILE PATH'
conn = sqlite3.connect("DBNAME.sqlite3")
curs =  conn.cursor()
try:
    curs.execute("CREATE TABLE t (webrank INTEGER, term, TEXT PRIMARY KEY, gloss TEXT);")
except sqlite3.OperationalError:
    print "Table already exist"
with open('{}.csv'.format(myfile), 'rb') as infile:
    dr = csv.DictReader(infile, delimiter = ',')
    def unicoded_data():
        for row in dr:
            # Assuming infile encoding is utf-8.
            yield int(row['WebRank']), unicode(row['term'], encoding='utf-8'), unicode(row['gloss'], encoding='utf-8')

    curs.executemany("INSERT INTO t (webrank, term, gloss) VALUES (?,?,?);", unicoded_data())
    conn.commit()

Collectives™ on Stack Overflow

CSV to sqlite3 database.Convert list from Utf8 to unicode

Edited based on input from answers

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edited based on input from answers

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related