SQLITE IMPORT CSV HOW TO
SQLITE IMPORT CSV WINDOWS

SQLITE IMPORT CSV WINDOWS

Unfortunately, since subprocess passes all follow-ons to -cmd as quoted strings, you need to double up your backslashes if you have a windows directory path. In the shell, each command has to be in quotes, but here, they just need to be their own element of the sequence

-cmd flag after the database allows you to pass multiple follow on commands to the sqlite program.

The argument to n() is a sequence of strings which are interpreted as a command followed by all of it's arguments. The -skip 1 argument will give an error prior to 3.32įrom the command line, the command you're looking for is sqlite3 my.db -cmd '.mode csv' '.import file.csv table'. If you have an older version of sqlite3, you may need to first create the table, then strip off the first row of the csv before importing. import command has improved so that it can treat the first row as header names or even skip the first x rows (requires version >=3.32, as noted in this answer.

In the example below, I assume the table already exists, but the csv file has headers in the first row. A lot of the top answers to this question involve native python loops, but if your files are large (mine are 10^6 to 10^7 records), you want to avoid reading everything into pandas or using a native python list comprehension/loop (though I did not time them for comparison).įor large files, I believe the best option is to use n() to execute sqlite's import command. import is the way to go, but that's a command from the SQLite3 command line program. Python’s great support for sqlite will make you love it in no time.You're right that. It’s a great database when you’d like relational database query functionality without the overhead of Postgres. Sqlite databases are great for local experimentation and are used extensively on mobile phones. Python’s build in sqlite library coupled with Pandas DataFrames makes it easy to load CSV data into sqlite databases. pd.read_sql('''SELECT * FROM users u LEFT JOIN orders o ON u.user_id = o.user_id''', conn) Next steps You can also read the SQL query directly into a Pandas DataFrame. Here’s the array that’s returned: [(1, 'pokerkid', 1, 1, 'speaker'), Join the users and orders tables on the user_id value and print the results: c.execute('''SELECT * FROM users u LEFT JOIN orders o ON u.user_id = o.user_id''')

Orders.to_sql('orders', conn, if_exists='append', index = False) # write to sqlite table Fetch results of database join Orders = pd.read_csv('orders.csv') # load to DataFrame c.execute('''CREATE TABLE orders (order_id int, user_id int, item_name text)''') Suppose you have the following orders.csv file: order_id,user_id,item_nameĬreate a table and then load the orders data into the database. Cursors can be thought of as iterators in the database world. The fetchall() method returns an array of tuples.Ĭ.execute() returns a sqlite3.Cursor object. Fetch values from sqlite tableįetch all the rows from the users table: c.execute('''SELECT * FROM users''').fetchall() # The to_sql method makes it easy to write DataFrames to databases. Users.to_sql('users', conn, if_exists='append', index = False) Pandas makes it easy to load this CSV data into a sqlite table: import pandas as pd Suppose you have the following users.csv file: user_id,username

c.execute('''CREATE TABLE users (user_id int, username text)''') Load CSV file into sqlite table import sqlite3Įxecute a query that’ll create a users table with user_id and username columns. You can create the file with touch my_data.db or with this equivalent Python code: from pathlib import PathĪ zero byte text file is a great starting point for a lightweight database! Creating sqlite tableĬreate a database connection and cursor to execute queries. Sqlite is a lightweight database that can be started as an empty text file. Python is perfect language for this task because it has great libraries for sqlite and CSV DataFrames.

SQLITE IMPORT CSV HOW TO

This blog post demonstrates how to build a sqlite database from CSV files.

Sqlite import csv