我正在使用pymssql将一个df写入mssql,但不起作用。
from sqlalchemy import create_engine
import pymssql
engine = create_engine('mssql+pymssql://sa:suzhou@localhost/test_python')
data.to_sql('phill',engine)并在下面显示错误。怎么了?
NoSuchColumnError: "Could not locate column in row for column '0'"前5行:
Dc_Dist Psa Dispatch_Date_Time Dispatch_Date Dispatch_Time Hour Dc_Key Location_Block UCR_General Text_General_Code Police_Districts Month Lon Lat
3 35 D 2009-07-19 01:09:00 2009-07-19 01:09:00 1 200935061008 5500 BLOCK N 5TH ST 1500.0 Weapon Violations 20.0 2009-07 -75.130477 40.036389
4 9 R 2009-06-25 00:14:00 2009-06-25 00:14:00 0 200909030511 1800 BLOCK WYLIE ST 2600.0 All Other Offenses 8.0 2009-06 -75.166350 39.969532
5 17 1 2015-04-25 12:50:00 2015-04-25 12:50:00 12 201517017705 800 BLOCK S BROAD ST 600.0 Thefts 13.0 2015-04 -75.166412 39.940070
6 23 K 2009-02-10 14:33:00 2009-02-10 14:33:00 14 200923006310 2200 BLOCK RIDGE AVE 800.0 Other Assaults 16.0 2009-02 -75.171149 39.979586
12 22 3 2015-10-06 18:18:00 2015-10-06 18:18:00 18 201522089374 1500 BLOCK N 15TH ST 600.0 Thefts 16.0 2015-10 -75.160229 39.976134发布于 2017-01-22 19:06:55
这是因为sqlalchemy带来的问题。在我的python sit包中,有SQLAlchemy-1.1.4-py2.7.egg-info、SQLAlchemy-1.1.5.dist-info和sqlalchemy。只需删除除sqlalchemy之外的所有内容,即可解决NoSuchColumnError: "Could not locate column in row for column '0'"问题。
发布于 2017-01-18 23:34:43
我猜SQL Server不喜欢像0这样的列名,所以在将DF写入SQL Server之前,您必须对列进行重命名。
因此,您可以尝试以下解决方案:
data.add_prefix('col_').to_sql('phill',engine)发布于 2017-01-18 23:54:00
from sqlalchemy import create_engine
import pyodbc
import pandas as pd
import numpy as np
df = pd.DataFrame(index=range(3), columns=range(3), data=np.zeros((3,3)))
engine = create_engine("mssql+pyodbc://.\SQLEXPRESS/test_python?driver=SQL server")
df.to_sql('test', con=engine)在本地服务器上运行良好,但使用pyodbc。你的数据帧是什么样子的?
https://stackoverflow.com/questions/41722498
复制相似问题