Update a SQLite3 database using CSVs and script automation

Question

I have a sqlite database that is populated with values from csv files. I would like to create a script that when run:

deletes the old tables
creates new tables with the same schema (with newly updated values)

I noticed that sqlite script files don't accept ".mode csv" or .import "csv". Is there a way to automate this is with a script of some sort?

Not clear, but I suppose you should use some gems, for example require "csv" and require "sqlite3" — iGian
– iGian, Commented Apr 26, 2018 at 14:44

Scratch'N'Purr · Accepted Answer · 2018-04-26 14:52:23Z

1

If you want a Python approach, you can use to_sql method from the pandas package to write to SQLite. Pandas can replace existing tables and automatically generate the schema based on the CSV file read.

import sqlite3
import pandas as pd

conn = sqlite3.connect('my.db')

# read the csv file
df = pd.read_csv("my.csv")

# write to SQLite
df.to_sql("my_tbl", conn, if_exists="replace")

conn.close()

answered Apr 26, 2018 at 14:52

Scratch'N'Purr

10.5k2 gold badges39 silver badges54 bronze badges

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

1 Comment

Josh Sharkey Over a year ago

awesome, this is exactly what I was looking for!

Collectives™ on Stack Overflow

Update a SQLite3 database using CSVs and script automation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related