Databases
StackOverflow2013
Postsdbo
POCSV to sqlite3 database.Convert list from Utf8 to unicode
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCSV to sqlite3 database.Convert list from Utf8 to unicode
    primarykey
    data
    text
    <pre><code>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) </code></pre> <p>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.</p> <blockquote> <p>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.</p> </blockquote> <h3>Edited based on input from answers</h3> <p>The revised code(below) executes successfully now but it does not insert the values taken from csv to the database.</p> <pre><code>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) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USshaytac
    1. USshaytac
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCSV to sqlite3 database.Convert list from Utf8 to unicode
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POCSV to sqlite3 database.Convert list from Utf8 to unicode
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat 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 ?
      singulars
      1. POCSV to sqlite3 database.Convert list from Utf8 to unicode
      1. USThomas Vander Stichele
    2. COWhen 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.
      singulars
      1. POCSV to sqlite3 database.Convert list from Utf8 to unicode
      1. USshaytac
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload