Is there any way to automatically load column data types into SQLite using SQLAlchemy?

Question

I have a large csv file with nearly 100 columns with varying data types that I would like to load into a sqlite database using sqlalchemy. This will be an ongoing thing where I will periodicly load new data as a new table in the database. This seems like it should be trivial, but I cannot get anything to work.

All the solutions I've looked at so far have defined the columns explicitly when creating the tables.

Here is a minimal example (with far fewer columns) of what I have at the moment.

from sqlalchemy import *
import pandas as pd

values_list = []
url = r"https://raw.githubusercontent.com/amanthedorkknight/fifa18-all-player-statistics/master/2019/data.csv"
df = pd.read_csv(url,sep=",")   
df = df.to_dict()
metadata = MetaData()
engine = create_engine("sqlite:///" + r"C:\Users\...\example.db")
connection = engine.connect()
# I would like define just the primary key column and the others be automatically loaded...
t1 = Table('t1', metadata, Column('ID',Integer,primary_key=True))
metadata.create_all(engine)
stmt = insert(t1).values()
values_list.append(df)
results = connection.execute(stmt, values_list)
values_list = []
connection.close()

Can you include a small sample of the data? Is all the data of the same type (e.g. Integer, Float, String)? — SuperShoot
– SuperShoot, Commented May 10, 2019 at 11:23
The sample url above is a good representation of the different data types present in the actual data — Kosmonaut
– Kosmonaut, Commented May 14, 2019 at 11:43

Kosmonaut · Accepted Answer · 2019-05-23 10:30:03Z

1

Thanks for the suggestions. After some time searching, a decent solution is using the sqlathanor package. There is a function called generate-model-from-csv which allows you to read in a csv (also available for dictionary, json, etc) and build a sqlalchemy model directly. It is imperfect on datatype recognition, but certainly will save you some time if you have a lot of columns.

https://sqlathanor.readthedocs.io/en/latest/api.html#generate-model-from-csv

answered May 23, 2019 at 10:30

Kosmonaut

12810 bronze badges

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

Collectives™ on Stack Overflow

Is there any way to automatically load column data types into SQLite using SQLAlchemy?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related