How to automatically change name if sql table exists after running the code in python?

Question

I am trying to run this code once a day to log the dataframes to make historical dataset.

I have connected mysql with pymysql to save my pandas dataframe into mysql using pymysql and converted pandas dataframe into sql using .to_sql method.

However, if I run this code 2 times, the name of the table overlaps and won't run 2nd time. Therefore I need to change the name(data_day001, data_day002, data_day003...) of the table each time I run this code.

# Credentials to database connection 
hostname="hostname"
dbname="sql_database"
uname="admin"
pwd="password"

# Create SQLAlchemy engine to connect to MySQL Database
engine = create_engine("mysql+pymysql://{user}:{pw}@{host}/{db}"
                .format(host=hostname, db=dbname, user=uname, pw=pwd))

# Convert dataframe to sql table                               
channel_data.to_sql('data_day001', engine, index=False)

Please advise me how I could solve this problem. Thank you so much in advance.

save numer in file - and next time read numer from file, add 1, and save it back in file. And us e this this number to generate name f"data_day{number:03}" — furas
– furas, Commented May 11, 2022 at 4:20
@furas that is exact approach I am looking for but I don't know how I could apply it on the to_sql() name instance. channel_data.to_sql(f"data_day{number:03}", engine, index=False) like this? — IPLab-H
– IPLab-H, Commented May 11, 2022 at 4:41

Corralien · Accepted Answer · 2022-05-11 04:46:02Z

2

Use the inspect function:

from sqlalchemy import create_engine, inspect

def get_table_name(engine):
   names = inspect(engine).get_table_names()
   return f"data_day{len(names):03}"

engine = create_engine(...)

channel_data.to_sql(get_table_name(engine), engine, index=False)

After some days:

>>> inspect(engine).get_table_names()
['data_day000', 'data_day001', 'data_day002', 'data_day003', 'data_day004']

answered May 11, 2022 at 4:46

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to automatically change name if sql table exists after running the code in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related