r0xette r0xette - 10 months ago 51
Python Question

Reading csv from url and pushing it in DB through pandas

The URL gives a csv formatted data. I am trying to get the data and push it in database. However, I am unable to read data as it only prints header of the file and not complete csv data. Could there be better option?


import pandas as pd
data = pd.read_csv("some-url") //URL not provided due to security restrictions.

for row in data:

Answer Source

You can iterate through the results of df.to_dict(orient="records"):

data = pd.read_csv("some-url")
for row in data.to_dict(orient="records"):
    # For each loop, `row` will be filled with a key:value dict where each
    # key takes the value of the column name.
    # Use this dict to create a record for your db insert, eg as raw SQL or
    # to create an instance for an ORM like SQLAlchemy.

I do a similar thing to pre-format data for SQLAlchemy inserts, although I'm using Pandas to merge data from multiple sources rather than just reading the file.

Side note: There will be plenty of other ways to do this without Pandas and just iterate through the lines of the file. However Pandas's intuituve handling of CSVs makes it an attractive shortcut to do what you need.