How to write dataframe into mssql using pymssql?

Question

I am using pymssql to write a df into mssql, but didn't work.

from sqlalchemy import create_engine
import pymssql
engine = create_engine('mssql+pymssql://sa:suzhou@localhost/test_python')

data.to_sql('phill',engine)

With an error below. What's wrong?

NoSuchColumnError: "Could not locate column in row for column '0'"

TOP 5 ROWS:

    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

Answer 1

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)

Works fine with a local server but using pyodbc. What does your dataframe look like?

Answer 2

guess SQL Server doesn't like column names like 0, so you would have to rename your columns before writing your DF into SQL Server.

So you can try the folowing solution:

data.add_prefix('col_').to_sql('phill',engine)

Answer 3

It's the problem because of sqlalchemy. In my python sit-packages, there are SQLAlchemy-1.1.4-py2.7.egg-info, SQLAlchemy-1.1.5.dist-info, and sqlalchemy. Just delete all except sqlalchemy, the NoSuchColumnError: "Could not locate column in row for column '0'" will be solved.