jeudi 13 août 2015

Writing DataFrame rows to different database Tables using re.match and SQLite

I have a DataFrame that looks the following way:

df_transpose=
Time                     Date  Morning (5AM-9AM)  Day (9AM-6PM)  \
Area                                                            
D1_NY_1         01_05_2012           0.000000       0.000000   
D2_NY_2         01_05_2012           0.000000       0.000000   
D3_NJ_1         01_05_2012           1.000000       0.966667
   ... 

I want to write this row-by-row to different tables in a database using SQLite. I've set up the database Data.db which contains separate tables for each Area - i.e. the table names contain the Area names as listed in the DataFrame above (ex "Table_D1-NY-1" ect.) in their names. I want to test if theres a match between the Area (the index) in the DataFrame above and the names of the tables in my database, and if there's a match write the entire relevant row of the DataFrame to the Table that contains the same Area in the name. Here is what I've written so far, as well as the error I get:

CODE:

    ii=0
    for ii in range(0,row_count):
        df_area= df_transpose.index[ii]
        export_data= df_transpose.iloc[ii]
        cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
        available_tables=(cur.fetchall())
        for iii in range (0, row_count):
            if re.match('\w*'+df_feeder, available_tables[iii]):
                relevant_table=available_tables[iii]
                export_data.to_sql(name=relevant_table, con=con, if_exists=append)
            iii=iii+1

ERROR: for the "if re.match..." line:

TypeError: expected string or buffer

I tried to make the second (iii)loop after searching for solutions to the problem to avoid inputting a list object (available_tables) -instead of a string- to re.match(). I still get the same error though. Can anyone see my error or help me fix my the code?

EDIT: For information, df_area and available_tables outputs the following:

df_area=
u'D1_NY_1

available_tables=
[(u'D1_NY_1',), (u'D2_NY_2',), (u'D3_NJ_1',), ...]

Aucun commentaire:

Enregistrer un commentaire