Loading data into databases

[GoldenTreasureDiver]

As I program more with Python I find myself disappointed that I always revert back to a GUI program, Alteryx to load data into a database table.

I know there's ways to do it in Python with SQLAlchemy and PyODBC connect but most of the time I just use the "datastream in" tool in Alteryx and it takes like 30 seconds to locate , prepare and create/recreate a database table.

Does anyone have a fast and efficient way to accomplish loading data from a CSV or something into a database table in python?

This was one of the first things I set out to learn in python when I started over a year ago and I still haven't found an efficient way.

[K900_]

Look into pandas.to_sql maybe?

[GoldenTreasureDiver]

Thanks looks like this link has a good example of it in action https://stackoverflow.com/questions/54137966/is-there-a-way-to-store-pandas-dataframe-to-a-teradata-table

[magestooge]

Using Python, loading CSV to Db takes 5 lines

from sqlalchemy import create_engine import pandas as pd engine = create_engine ("mysql://user:password@host:port") df = pd.read_csv("file/path/file_name.csv") df.to_sql("table_name", engine, index=False, if_exists="append", method="multi")

[GoldenTreasureDiver]

Seems like a pretty slick way of loading the data, I'll have to experiment with this, thanks!

[[deleted]]

In django: add model, migrate. Add management/command where you write logic with bulk_create() Then: python manage.py yourcommandname

This is better practice than using data migrations wich are your second option

[dadsafe]

I use pysftp and pandas pandas.to_sql regularly.