0

I got a number of dataframes with information associated with different systems. Now I'm trying to write the inforamtion to multiple tables (number of systems I got) by using sqlAlchemy. (I'm pretty new to python and sqlAlchemy tho)

So I'm wondering if theres a nicer possibility to write the values of each column of the dataframe to DIFFERENT tables? E.g. Column 1 of dataframe 3, 4 to table 1, column 2 of dataframe 3, 4 to table 2, and so on.

Also I keep getting the integrity error "Duplicaty entry" if any values are written twice to the same column in the table.

x = 0
for index in a_id:
    table_sim = Table(
        f'simulated_for_sys_{np.int_(index)}', meta,
        Column('timestamp', DateTime, primary_key = True),
        Column('system__id', Integer),
        Column('simulated_yield_in_kWh', Float),
        Column('global_irradiance_tilted_in_kWh_per_m2', Float)
    )
    
    # Checking if table already exist
    if not engine.dialect.has_table(engine, f'simulated_for_sys_{np.int_(index)}'):
        print("Tables created", table_sim)
        # Specified table
        meta.create_all(engine)
    else:
        print("Table already exists...")

    conn = engine.connect()
    # Write timestamps from 01.01.xxxx till now
    for timestamp_utc in timestamp_df['timestamp_utc']:
        print(timestamp_utc)
        ins = table_sim.insert().values(timestamp = timestamp_utc.to_pydatetime() )
        result = conn.execute(ins)

    # Write id to table (giving duplicate error..)
    for system_id in sys_ids_df['system_id']:
        ins1 = table_sim.insert().values(system__id = system_id)
        conn = engine.connect()
        result = conn.execute(ins1)
        
    # Write pr information from column x of dataframeto table (also duplicate error in between if 
    # same values appear)
    colname = f'pr_{x}'
    for colname in pr_daily_df[f'{colname}']:
        ins2 = table_sim.insert().values(simulated_yield_in_kWh = colname)
        result = conn.execute(ins2)       
    
    # Write rad information from column x of dataframe to table (also duplicate error in between if 
    # same values 
    appear)
    colname = f'rad_{x}'
    for colname in rad_daily_df[f'{colname}']:
        ins3 = table_sim.insert().values(global_irradiance_tilted_in_kWh_per_m2 = colname)
        result = conn.execute(ins3)
        
    x += 1
Timo K
  • 27
  • 5
  • duplicate entry is probably because it is a unique column/primary key column. – Z Li Dec 09 '20 at 14:12
  • Can I change this somewhere when creating the table with SQLAlchemy? @ZLi – Timo K Dec 09 '20 at 14:22
  • You set `primary_key = True`, which is a good practice to have a primary key. So the best solution is to do on_conflict_do_update (upsert) to insert duplicated values. – Z Li Dec 09 '20 at 14:27
  • Where exactly do I have to add this? – Timo K Dec 09 '20 at 14:32
  • see this: https://stackoverflow.com/questions/7165998/how-to-do-an-upsert-with-sqlalchemy – Z Li Dec 09 '20 at 14:34

0 Answers0