前端之家

  1. 首页
  2. 问答

sqlalchemy.exc.ProgrammingError：（pyodbc.ProgrammingError）（'42S22'，“ [42S22] [Microsoft] ..... SQL Server]无效的列名'0'

• 问答

我正在尝试使用to_sql（）函数来更新现有的SQL数据库。..

当我尝试运行代码时，我发现此错误

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,index=index,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,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",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,898),(1,'bts005811',11063,1091),(2,'bts089431',53462,3170,1111),(3,'bts088191',47274,3200,1108),(4,'bts026851',49386,1145),(5,'bts026911',52128,1054),(6,45101,1106),(7,'bts005541',10970,1090)  ... displaying 10 of 1200 total bound parameter sets ...  (1198,'bts005331',10902,1187),(1199,1187))]
(Background on this error at: http://sqlalche.me/e/f405)

这是我的代码：

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',index=True)

数据库部分：

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)

请注意，Excel文件和数据库sql之间的列名称有所不同...不同之处在于SQL数据库中没有空格，而Excel文件中有空格

wspanwb 回答：sqlalchemy.exc.ProgrammingError：（pyodbc.ProgrammingError）（'42S22'，“ [42S22] [Microsoft] ..... SQL Server]无效的列名'0'

[SQL：插入[MNM_Rotterdam_5_Daily_Details-20191216081027]（[index]，[0]，[1]，[2]，> [3]，[4]，[5]）值（？，？，？ ，？，？，？，？）]

不能使用'0，1，2，3 ....'作为列名

,

请注意，Excel文件和数据库sql之间的“列”名称有所不同... 区别在于SQL数据库中没有空格，而Excel文件中没有空格

这是您的问题，您的目标列必须与目标数据库列匹配，否则它将如何知道要匹配的列？

查看This答案以了解更多信息。

可以通过以下两种方法之一来解决问题，

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

比较两列
pandaspandas-to-sqlsqlalchemy
本文链接：https://www.f2er.com/2813128.html

大家都在问