Populating a SQLite3 database from a .txt file with Python

user_363268 0
Viewed 7187

I am trying to setup a website in django which allows the user to send queries to a database containing information about their representatives in the European Parliament. I have the data in a comma seperated .txt file with the following format:

Parliament, Name, Country, Party_Group, National_Party, Position

7, Marta Andreasen, United Kingdom, Europe of freedom and democracy Group, United Kingdom Independence Party, Member

etc....

I want to populate a SQLite3 database with this data, but so far all the tutorials I have found only show how to do this by hand. Since I have 736 observations in the file I dont really want to do this.

I suspect this is a simple matter, but I would be very grateful if someone could show me how to do this.

Thomas

sqlite django python
6 Answers

If you want to do it with a simple method using sqlite3, you can do it using these 3 steps:

$ sqlite3 db.sqlite3
sqlite> .separator ","
sqlite> .import myfile.txt table_name

However do keep the following points in mind:

  1. The .txt file should be in the same directory as your db.sqlite3,
    otherwise use an absolute path "/path/myfile.txt" when importing
  2. Your schema for tables (number of columns) should match with the number of values seperated by commas in each row in the txt file

You can use the .tables command to verify your table name

SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> .tables
auth_group                  table_name               
auth_group_permissions      django_admin_log          
auth_permission             django_content_type       
auth_user                   django_migrations         
auth_user_groups            django_session            
auth_user_user_permissions
user_84077190
Related