2020年1月6日 23:23:06go评论78阅读模式

英文:

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

问题

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

<details>
<summary>英文:</summary>

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 = &quot;Insert INTO [myDB].[dbo].[mnm_rotterdam_5_daily_details-20191216081027] (GBSC, SiteName, CellCI, CellLAC, CellName, CellIndex) values (?,?,?,?,?,?)&quot;
val = insert_zip
for v in val:
print(v)


Note That, there&#39;s a difference in Columns name between the Excel file and the database sql... The difference is there&#39;s no spaces in SQL Data-Base and there&#39;s Spaces in the Excel file
</details>
# 答案1
**得分**: 1
你不能将`0, 1, 2, 3 ...`用作你的列名。
<details>
<summary>英文:</summary>

[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.
</details>
# 答案2
**得分**: 1
"请注意，Excel文件和数据库SQL之间的列名存在差异...
差异在于SQL数据库中没有空格，而Excel文件中有空格。
这就是您的问题，目标列*必须*与目标数据库列匹配，否则它将不知道要匹配哪些列。
查看[此](https://stackoverflow.com/questions/34771256/how-does-pandas-to-sql-determine-what-dataframe-column-is-placed-into-what-datab)答案获取更多信息。
解决您的问题有两种方法之一，
```python
new_df.columns = new_df.columns.str.replace('\s+', '')

或者您可以调用您的数据库，

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

然后通过调用 df.columns 来比较两列。"

英文:

>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(&#39;\s+&#39;,&#39;&#39;)

or you could call your database

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

then you can compare both columns by calling df.columns

答案3

得分: 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(&quot;SELECT * from names where n like &#39;AA&#39; &quot;)

This doesn't:

cursor.execute(&#39;SELECT * from names where n like &#39;AA&#39; &#39;)

英文:

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(&quot;SELECT * from names where n like &#39;AA&#39; &quot;)

This doesn't:

cursor.execute(&#39;SELECT * from names where n like &#39;AA&#39; &#39;)

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

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

问题

答案3

Python: ProcessPoolExecutor vs ThreadPoolExecutor

Read .csv file with columns of varying length as dictionary in Python.

TypeError when sum value of dictionary for each key

Pushing polar dataframe to redis

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论