[sqlite3] Creating a sqlite database from CSV file

1. How to create a sqlite database from CSV file? 

Step 1 : Creating a sqlite database

sqlite is a ligthweight database that can be started as an empty text file. We can create the file with touch data.db.

 

from pathlib import Path
Path('cars.db').toucn()

 

Step 2 : Create a connection and cursor object

Create a database connection and cursor to execute queries. We can use functions called connect and cursor for this work.

 

# Create objects 
import sqlite3
conn = sqlite3.connect('data.db') 
cursor = conn.cursor() 

# Create object with with comprehension 
with sqlite3.connect('data.db') as conn: 
	cursor = conn.cursor()

 

Step 3 : Create a sqlite table

Execute a query which create a table with our csv file's columns.

 

cursor.execute("""
CREATE TABLE cars (
     car_ID INTEGER PRIMARY KEY,
     symboling FLOAT NOT NULL, 
     CarName TEXT NOT NULL, 
     fueltype TEXT NOT NULL,
     aspiration TEXT NOT NULL, 
	 ... 
);
""")

 

Step 4 : Load file into sqlite table

The python library Pandas make loading csv file into sqlite table easy using to_sql method.

 

# Load csv data into a sqlite table 
import pandas as pd 
cars = pd.read_csv('Datasets/CarPrice_Assignment.csv')
cars.to_sql('cars', conn, if_exists='append', index=False)

 

Step 5 : Check the result

The python library Pandas make easy to get table for input query using read_sql method.

# Check the result 
pd.read_sql("SELECT * FROM cars", conn)

# Close the connection
conn.close()