Python and sqlite3 - import text file into database

Question

Thanks for your help. I am new to SQLite3 and was indeed mixing up the two. I have tried the code below but it still does not work.

import csv
import sqlite3

# Create the database
connection = sqlite3.connect('addresses.db')
cursor = connection.cursor()

# Create the table
cursor.execute('DROP TABLE IF EXISTS addresses')
cursor.execute('''CREATE TABLE addresses
             (ID text, Firstname text, Surname text, Address1 text, Address2 text, Postcode text)''')
connection.commit()

# Load the CSV file into CSV reader
csvfile = open('addresses.txt', 'r')
creader = csv.reader(csvfile, delimiter=',', quotechar='"')

# Iterate through the CSV reader, inserting values into the database
for t in creader:
    cursor.execute('INSERT INTO  addresses VALUES (?,?,?,?,?,?)', t )

# Close the csv file, commit changes, and close the connection
csvfile.close()
connection.commit()
connection.close()

I get the error
for t in creader: File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/encodings/ascii.py", line 26, in decode return codecs.ascii_decode(input, self.errors)[0] UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 0: ordinal not in range(128)

you say you want to load a file called names.txt into the database, but you do actually load a file addresses.txt… Sorry for raising the obvious, but sometimes… :-) — zmo
Sorry, my typo. Thanks for spotting - I wish it was that simple! — g3561

Daniel Roseman Daniel Roseman · Accepted Answer · 2014-12-28T17:39:00

You didn't show the error you are getting.

However, it seems that you are confused between sqlite3 and MySQL. These are two completely separate databases, yet your question title refers to "mysqlite3". You connect to a sqlite db file in your code, but then you try to run LOAD DATA which is a MySQL-specific command not supported by sqlite.

You will have to write some Python code to read the CSV file, iterate through, and insert each row to the database. The csv module will help.

Python and sqlite3 - import text file into database

2 Answers