pandas add sql table, check if table exists

Question

I have this piece of code that loops for excel files in a directory, add the file in a sqlite db . I managed to get past the exception raised if the table exists but I find this inelegant and inefficient since the loop reads the excel file, add it in a Dataframe, etc... what ideally i would like is that I test the existence of the table before creating the df from excel.

response = {}
for f in glob('T:\GESTION\toto\titi\tata\file_201*.xlsx'):
    print f
    datereg = re.search('T:\\\\GESTION\\\\toto\\\\titi\\\\tata\\\\file_(\d{4})(\d{2})(\d{2}).xlsx', f)
    if datereg is not None:
        dated = datetime.datetime(int(datereg.group(1)), int(datereg.group(2)), int(datereg.group(3)))
    print dated

    # ideally test if table in db exists here

    xl = pd.ExcelFile(f)
    df = xl.parse(sheetname="Sheet1")
    df = df[extractFields].drop_duplicates(subset='ISIN')
    df = df.set_index('ISIN', verify_integrity=True)
    response[dated] = df
    # print response
    engine = sqlalchemy.create_engine('sqlite:///my_db.sqlite')
    try:
        df.to_sql(dated.__str__(), engine, if_exists='fail')
    except ValueError as err:
        print(err)
        pass

Why not just create an empty df after you generate your dated datetime and test at that point, this is before you even read the excel file and then call df.to_sql at that point — EdChum
– EdChum, Commented Jan 14, 2015 at 9:41
when you say test at that point do you mean use the try: except on the empty df ? — euri10
– euri10, Commented Jan 14, 2015 at 10:09
with engine.has_table('table_name') you can check if a table already exists — joris
– joris, Commented Jan 14, 2015 at 11:59

Pruthvi Kumar · Accepted Answer · 2019-11-08 00:30:14Z

5

This is what i have for my bit of code:

engine = create_engine("mysql+mysqlconnector://{user}:{pw}@localhost/{db}".format(user="root",pw="password",db="worker"))
check=engine.has_table(table_name)
            print(check) #boolean
            if check == False:
                df.to_sql(con=engine, name=table_name, if_exists='replace',index=False)

edited Nov 8, 2019 at 0:30

Pruthvi Kumar

9081 gold badge7 silver badges15 bronze badges

answered Aug 2, 2018 at 16:18

chrisroker0

1502 silver badges7 bronze badges

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

1 Comment

Alex Riabov Over a year ago

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

Jiwoo Choi · Accepted Answer · 2021-07-29 08:29:24Z

3

From sqlalchemy 1.4, has_table is now called from Inspector object.

Check out the documentation

from sqlalchemy import inspect, create_engine

engine = create_engine('...')
insp = inspect(engine)
table_exist = insp.has_table(table_name, schema)

if table_exist:
    df.to_sql(name, engine)

answered Jul 29, 2021 at 8:29

Jiwoo Choi

413 bronze badges

Collectives™ on Stack Overflow

pandas add sql table, check if table exists

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related