CODEWITHSUNDEEP
pythonsql-serverpandas
Umar.H
Umar.H

Reputation: 23099

Writing pandas df to sql dB with a new column not working with append

trying to do some self-learning with pandas/ sql alchemy. I've set up a dummy dB ms SQL 2012 (dB we use at work) on my local system and I can

read data from it

create a table from an existing dB to it (tested up to 5 million rows of ints and short strings which took about 9 minutes)

update it with and append new rows

replace all target rows with df with if_exists='replace'

now what I can't figure out, and this is probably due to my ignorance is how to write a dataframe with an additional column into my dB.

take the following df which I would pull from my dB

   stores = np.random.choice(800,5,replace=True)
week1 = np.random.randint(1,500,size=5)
df = pd.DataFrame({'Stores' : stores,'Week 1' : week1})
    print(df)
    Stores  Week 1
0   461 413
1   568 181
2   793 173
3   349 49
4   713 258

now if I wanted to update this with the existing columns and new rows this works perfectly as expected, but if I create a new column :

df['Week 2'] = np.random.randint(1,500,size=len(df))

I get the following error when using :

df.to_sql(name='my table',
con = engine,
index=False,
if_exists='append')



    ProgrammingError: ('42S22', "[42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Week 2'. (207) (SQLExecDirectW); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Week 2'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")

The above exception was the direct cause of the following exception:

any help/documentation guidance is greatly appreciated as always.

Upvotes: 0

Views: 1053

Answers (1)

Damian
Damian

Reputation: 568

As written in error description, "Week 2" is unacceptable name of table column in Mysql "Invalid column name 'Week 2'.",

ProgrammingError: ('42S22', "[42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Week 2'. (207) (SQLExecDirectW); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '1956'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")

Change it to (for example) Week2 and it should work properly.

df['Week2'] = np.random.randint(1,500,size=len(df))

Also it sees "1956" as column name but I don't know if it's because of previous error or not so please correct this one first ;)

Upvotes: 1

Related Questions