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
– zmo, Commented Dec 28, 2014 at 17:17
Sorry, my typo. Thanks for spotting - I wish it was that simple! — g3561
– g3561, Commented Dec 29, 2014 at 19:50

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

2

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.

answered Dec 28, 2014 at 17:39

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

2 Comments

Anzel Over a year ago

As a side note, even better after reading the csv and construct the fields into a list, then do a executemany to insert in bulk.

g3561 Over a year ago

I've attempted to use the csv module but receive an error. I've edited the initial question. Thanks for your help so far.

gosom · Accepted Answer · 2014-12-28 17:39:34Z

-1

As far as I know LOAD DATA LOCAL INFILE is not supported by sqlite.

You should parse the file line by line and generate the inserts or use the import command

https://www.sqlite.org/cli.html

search for .import

answered Dec 28, 2014 at 17:39

gosom

1,3173 gold badges16 silver badges38 bronze badges

1 Comment

user5359531 Over a year ago

this is a Python question, referring to the CLI documentation is not terribly helpful

Collectives™ on Stack Overflow

Python and sqlite3 - import text file into database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related