How To Write Dataframe Into Mssql Using Pymssql?

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_p

Solution 1:

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)

Solution 2:

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?

Solution 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.