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

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.

CodePudding user response：

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']