How to preserve pandas dataframe columns using to_sql

Question

I want to store some data from dataframes into an sqlite database. I am doing the following:

first create a database:

conn = sqlite3.connect("mydatabase.db")

then add table from dataframe df to database:

df.to_sql(table_name, conn,if_exists='append')

then querying the table from the database:

cur = conn.cursor()
cur.execute("SELECT * FROM table_name")
rows = cur.fetchall()
df2=pd.DataFrame(rows)

df2 does not have the original common names but has been reindex to 0, 1,2,3 etc.

How can I preserve the columns of the original dataframe?

Thank you

can you post an output of print(df.columns.tolist()) before you write to SQLite? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Nov 15, 2017 at 15:46
it's a list of strings, but some of them contain spaces. could that create the issue? — user7188934
– user7188934, Commented Nov 15, 2017 at 15:51

Steven G · Accepted Answer · 2017-11-15 15:57:07Z

3

try this:

df2 = pd.read_sql("SELECT * FROM table_name", conn)

some color:

your table is fine, it has the columns, you fetch a list of tuple of values using cur.fetchall() it does not have any info for columns name so pandas just use numbers.

read_sql() check for column names and does it for you

edited Nov 15, 2017 at 15:57

answered Nov 15, 2017 at 15:51

Steven G

17.3k11 gold badges58 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

How to preserve pandas dataframe columns using to_sql

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