jake wong jake wong - 6 months ago 22
SQL Question

Can I create a loop to update SQL database?

I have 6 tables in my

file. And I was wondering if it would be possible to create a loop to go through each column within this 6 tables, and convert the values to
if the cell value is

Code I currently have

for each_item in df.index:
# Note: The name of the tables in the database is the variable 'each_item'
pd.Dataframe.to_sql(df, con=engine, name=each_item, if_exists='append', index=False)

# The below code does not work. And I have no idea why
for each_columns in df.columns:
connection.execute('UPDATE each_item SET each_columns = NULL WHERE each_columns = '-')

This seems to produce an error.

How should I be coding it such that I am able to go through all the
in the
, and update each and every
in the
, if the cell value =

To be more specific, The error I got says that it cant locate the table.
no such table: each_item


Ok, there are quite a few problems in your code. Let's address each at a time:

  1. pd.DataFrame.to_sql stands for your_dataframe.to_sql in this case, df.to_sql. It is a method on a DataFrame object, and that object is the DataFrame you created. If this confuses you, please read about classes and methods in Python.
  2. to_sql takes its first argument as table name, then connection, then schema name, and finally the if_exists and the index kwag
  3. It would be better if you did all conversions before you tried to write to SQL. That just makes things cleaner. Further, if you DataFrame is properly dtyped, NaN values will automatically get converted to the appropriate NULL value representation for the database engine in question.

If you consider all the points above, this is how it should be done:

for each_item in df.index: # I would avoid a syntax like this. Better to have the DataFrames in a list, than iterate through a DataFrame
    df.loc[each_item].to_sql(name=each_item, con=engine, if_exists='append', index=False) # Here again, I would avoid using the name engine for a connection to a database engine

However, the success of the above code depends on your DataFrame being properly data typed, which I conclude it is not because you asked this question. If you edit your question with a sample of your DataFrame, I would be able to help you get your dtypes correct.

Ps. If your DataFrame is created as a result of your previous question about DataFrame merging, then let me know and I will provide you with a comprehensive solution to both questions here. But do edit this question with data from that question so that people reading this question will not be stumped.