How do I import a text file into a SQLite3 table using python

Question

I currently have a txt file of movies which contain a movieID,Movie Name, and genre. Example:

1,Toy Story (1995),Animation|Children's|Comedy
2,Jumanji (1995),Adventure|Children's|Fantasy
3,Grumpier Old Men (1995),Comedy|Romance
4,Waiting to Exhale (1995),Comedy|Drama
5,Father of the Bride Part II (1995),Comedy

I am trying to import this file into a SQLite3 table created with Python:

import sqlite3

conn = sqlite3.connect('CSC 452 Final.db')
c=conn.cursor()

Drop Tables

c.execute('''DROP TABLE IF EXISTS temp_movie''')
print("Table temp_movie dropped.")

Create Table for Movies

c.execute('''CREATE TABLE temp_movie (
    MovieID          NUMERIC (5),
    movie_title_year VARCHAR2 (255),
    movie_categories VARCHAR2 (255),
    CONSTRAINT movies_movieID_PK PRIMARY KEY (
        MovieID
    ))''')
print("Table temp_movie successfully created.")
conn.commit()

What's the best way to import my text file into this database with the delimiter being ','?

skip the song and dance with python, and use sqlite3 itself. Use import or CSV virtual tables (sqlite.org/csv.html) — gregory
– gregory, Commented Aug 11, 2018 at 20:35

Ajax1234 · Accepted Answer · 2018-08-11 19:09:40Z

1

You can read in the txt file and split on ,:

file_data = [i.strip('\n').split(',') for i in open('filename.txt')]
new_data = [[int(a), *b] for a, *b in file_data] #convert string id to integer
conn = sqlite3.connect('CSC 452 Final.db')
c = conn.cursor()
c.executemany('INSERT INTO temp_movie VALUES (?, ?, ?)', new_data)

answered Aug 11, 2018 at 19:09

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

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

1 Comment

Mario Mason Over a year ago

I tried this and I got this error:Traceback (most recent call last): File "/Users/romariomason/Desktop/Graduate School/CSC 452/Final/CSC 452 Final.py", line 7, in <module> file_data = [i.strip('\n').split(':') for i in open('movies.txt')] ... UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 3041: invalid continuation byte

dilkash · Accepted Answer · 2018-08-11 19:13:44Z

1

I would have used python csv module to do that:

with open(<txt file>,'rb') as f: 
    dic_reader = csv.DictReader(f)
    value_list = [(each_row['MovieID'], each_row['movie_title_year'], each_row['movie_title_year']) for each_row in dic_reader]

c.executemany("INSERT INTO t (MovieID, movie_title_year, movie_title_year) VALUES (?, ?);", value_list)
conn.commit()
conn.close()

answered Aug 11, 2018 at 19:13

dilkash

5924 silver badges15 bronze badges

1 Comment

CL. Over a year ago

@MarioMason Does that assignment allow you to copy the code from the internet?

Collectives™ on Stack Overflow

How do I import a text file into a SQLite3 table using python

Drop Tables

Create Table for Movies

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Drop Tables

Create Table for Movies

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related