sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S22', "[42S22] [Microsoft].....SQL Server]Invalid column name '0'

Question

I am trying to update existing SQL-Data base by using to_sql() Function.....

When I am trying to run the code I find this error

Traceback (most recent call last):
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1225, in _execute_context
    self.dialect.do_executemany(
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\dialects\mssql\pyodbc.py", line 399, in do_executemany
    super(MSDialect_pyodbc, self).do_executemany(
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\default.py", line 578, in do_executemany
    cursor.executemany(statement, parameters)
pyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '0'. (207) (SQLExecDirectW); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '1'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '2'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '3'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '4'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '5'. (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:

Traceback (most recent call last):
  File "D:/Tooling/20200206/MyDB_Update.py", line 22, in <module>
    new_df.to_sql('MNM_Rotterdam_5_Daily_Details-20191216081027', con=mydb, if_exists='append', index=True)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\core\generic.py", line 2530, in to_sql
    sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\sql.py", line 458, in to_sql
    pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\sql.py", line 1174, in to_sql
    table.insert(chunksize, method=method)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\sql.py", line 686, in insert
    exec_insert(conn, keys, chunk_iter)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\sql.py", line 599, in _execute_insert
    conn.execute(self.table.insert(), data)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 982, in execute
    return meth(self, multiparams, params)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1095, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1225, in _execute_context
    self.dialect.do_executemany(
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\dialects\mssql\pyodbc.py", line 399, in do_executemany
    super(MSDialect_pyodbc, self).do_executemany(
  File "C:\Users\mwx825326\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\default.py", line 578, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S22', "[42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '0'. (207) (SQLExecDirectW); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '1'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '2'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '3'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '4'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '5'. (207); [42S22] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")
[SQL: INSERT INTO [MNM_Rotterdam_5_Daily_Details-20191216081027] ([index], [0], [1], [2], [3], [4], [5]) VALUES (?, ?, ?, ?, ?, ?, ?)]
[parameters: ((0, 'RTWAGB2', 'bts038831', 50660, 3180, 50660, 898), (1, 'RTWAGB2', 'bts005811', 11063, 3180, 11063, 1091), (2, 'RTWAGB2', 'bts089431', 53462, 3170, 53462, 1111), (3, 'RTWAGB2', 'bts088191', 47274, 3200, 47274, 1108), (4, 'RTWAGB2', 'bts026851', 49386, 3200, 49386, 1145), (5, 'RTWAGB2', 'bts026911', 52128, 3180, 52128, 1054), (6, 'RTWAGB2', 'bts088191', 45101, 3200, 45101, 1106), (7, 'RTWAGB2', 'bts005541', 10970, 3180, 10970, 1090)  ... displaying 10 of 1200 total bound parameter sets ...  (1198, 'RTWAGB2', 'bts005331', 10902, 3170, 10902, 1187), (1199, 'RTWAGB2', 'bts005331', 10902, 3170, 10902, 1187))]
(Background on this error at: http://sqlalche.me/e/f405)

and this is my code:

from sqlalchemy import create_engine
import pandas as pd
from MyDB import Add_To_DB
mydb = create_engine(
    'mssql+pyodbc://WWX542337CDCD\SMARTRNO_EXPRESS/myDB?driver=SQL+Server+Native+Client+11.0')  # name of database
mycursor = mydb.raw_connection().cursor()
myDB = Add_To_DB.mydb
myCursor = Add_To_DB.mycursor
df = pd.read_excel('MNM_Rotterdam_5_Daily_Details-20191216081027 - Copy (2).xlsx',sheet_name='GSM Details')
#pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 100000)

print(df)
date=df['Date']
GBSC=df['GBSC']
Site_Name=df['Site Name']
Cell_CI=df['Cell CI']
cell_LAC=df['Cell LAC']
cell_name=df['Cell Name']
cell_index=df['CellIndex']
site_name = df['Site Name']


comp_zip=zip(GBSC,Site_Name,Cell_CI,cell_LAC,cell_name,cell_index)
new_df=pd.DataFrame(comp_zip)
new_df.columns  = new_df.columns.str.replace('\s+','')
new_df.to_sql('MNM_Rotterdam_5_Daily_Details-20191216081027', con=mydb, if_exists='append', index=True)

DB Part:

from sqlalchemy import create_engine

class Add_To_DB():
    # connect db
    mydb = create_engine('mssql+pyodbc://WWX542337CDCD\SMARTRNO_EXPRESS/myDB?driver=SQL+Server+Native+Client+11.0')
    mycursor = mydb.raw_connection().cursor()

    def insert_into_db(insert_zip, mycursor, mydb):
        sql = "Insert INTO [myDB].[dbo].[mnm_rotterdam_5_daily_details-20191216081027] (GBSC, SiteName, CellCI, CellLAC, CellName, CellIndex) values (?,?,?,?,?,?)"
        val = insert_zip
        for v in val:
            print(v)

Note That, there's a difference in Columns name between the Excel file and the database sql... The difference is there's no spaces in SQL Data-Base and there's Spaces in the Excel file

two possible issues, one your columns are integers, transform them `df.columns = df.columns.astype(str)` second, you don't have matching target columns in `MNM_Rotterdam_5_Daily_Details-20191216081027` check the columns match _exactly_ — Umar.H, Jan 06 '20 at 15:26
Does this answer your question? [How does Pandas to\_sql determine what dataframe column is placed into what database field?](https://stackoverflow.com/questions/34771256/how-does-pandas-to-sql-determine-what-dataframe-column-is-placed-into-what-datab) — Umar.H, Jan 06 '20 at 15:50
@Datanovice `AttributeError: Can only use .str accessor with string values (i.e. inferred_type is 'string', 'unicode' or 'mixed')` same error :( — Mahmoud Al-Haroon, Jan 06 '20 at 16:47
Strange, I can't replicate your error, I just tried it in a fresh VM works fine. — Umar.H, Jan 06 '20 at 16:48

score 1 · Answer 1 · edited Dec 31 '21 at 19:20

1

[SQL: INSERT INTO [MNM_Rotterdam_5_Daily_Details-20191216081027] ([index], [0], [1], [2], >[3], [4], [5]) VALUES (?, ?, ?, ?, ?, ?, ?)]

You cannot use 0, 1, 2, 3 ... as your column names.

edited Dec 31 '21 at 19:20

answered Jan 06 '20 at 15:37

Leona.li

426
4
4

thanks alot for your answer but please Could you check my DB part I just post it now :) – Mahmoud Al-Haroon Jan 06 '20 at 15:41
1. "if_exists='append' " means when the table 'MNM_Rotterdam_5_Daily_Details-20191216081027' has already exists, the insert sql must use the exactly same column in that table. – Leona.li Jan 06 '20 at 15:59
2. to_sql sentence doesnt use "index_lable", and the index name will be used as column name in the insert sql. and your df's index name is [0, 1, 2...]. which doesnot match table 'MNM_Rotterdam_5_Daily_Details-20191216081027's column. Thats why it failed – Leona.li Jan 06 '20 at 16:02
3. Two way to correct it. The first is rename you df's index name with your table's column name. The second is set "index_lable" in the to_sql sentence. – Leona.li Jan 06 '20 at 16:03
1

4. I am not a english speaker. If my reply makes you feel impolite or something weired, I am sorry for that . Hope this can help you . – Leona.li Jan 06 '20 at 16:06

score 1 · Answer 2 · answered Jan 06 '20 at 15:50

1

Note That, there's a difference in Columns name between the Excel file and the database sql... The difference is there's no spaces in SQL Data-Base and there's Spaces in the Excel file

That's your issue, your target columns must match your target database columns, how else would it know what columns to match?

Check This answer for more information.

the solution to your problem could be done in one of two ways,

new_df.columns  = new_df.columns.str.replace('\s+','')

or you could call your database

df = pd.read_sql("SELECT * TOP 1 from MNM_Rotterdam_5_Daily_Details-20191216081027",con=mydb)

then you can compare both columns by calling df.columns

answered Jan 06 '20 at 15:50

Umar.H

22,559
7
39
74

I tried your code and I find this error `AttributeError: Can only use .str accessor with string values (i.e. inferred_type is 'string', 'unicode' or 'mixed')`, I'll try again after your update :) – Mahmoud Al-Haroon Jan 06 '20 at 15:53
I just tried this on my own dataset, it works fine. are you sure you called it on `new_df.columns` ? – Umar.H Jan 06 '20 at 15:54
what is `df` `print(type(df))` – Umar.H Jan 06 '20 at 16:10
it is `` – Mahmoud Al-Haroon Jan 06 '20 at 16:42

score 0 · Answer 3 · edited Dec 31 '21 at 19:19

0

I get the error with writing in Python code. If anyone use Python to connect with SQL as I do, the problem will be fixed if you replace ' with ".

This works:

cursor.execute("SELECT * from names where n like 'AA' ")

This doesn't:

cursor.execute('SELECT * from names where n like 'AA' ')

edited Dec 31 '21 at 19:19

answered Dec 31 '21 at 19:17

husseinhadam

1
1

Note that you can also escape the single quotes: `\'AA\'` – Dec 31 '21 at 19:19

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S22', "[42S22] [Microsoft].....SQL Server]Invalid column name '0'

3 Answers3