0

I have a dictionary of lists that I need to write to a SQL Server where the dictionary keys are the column names and the dictionary values are large lists of values (currently ~30,000, will likely grow to 50,000+).

The data comes from a REST API, is transformed and some new values created and then written.

I have a working code that was running on Azure that is using pandas to do this but when the table grew from 80 columns to 110 columns, Azure started to crash, so now I have written something to take care of all the data transformations in a dictionary, I just need to write that dictionary to SQL.

A small example of the dictionary would look like this:

data_dict = {'company': [1, 1, 15, None], 'net_amount': [$200, $250, $150, $100], 'transaction_id': [11111, 11111, 11112, 11113]}

I thought of concatenating it into a string but that would create vulnerabilities and I know there has to be a library that can do it but is lighter weight than pandas.

Any help would be greatly appreciated.

I'm running 3.7+ and currently using SQLAlchemy to create the engine. Open to preparing the statement as a string or using a library other than pandas.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paul Clarke
  • 328
  • 2
  • 13

1 Answers1

1

For anyone searching in future, I managed to find a solution by converting the dictionary of lists to a list of dictionaries. This can then be uploaded via SQLAlchemy.

data_list = [dict(zip(data_dict, t)) for t in zip(*data_dict.values())]

engine = sqlalchemy.create_engine(
       "mssql://......",
       echo=False)
meta = MetaData()
meta.reflect(engine, only=['TABLENAME'])
table = sqlalchemy.Table('TABLENAME', meta, autoload=True, autoload_with=engine)
engine.execute(table.insert(), data_list)
Paul Clarke
  • 328
  • 2
  • 13