How to load data from dataFrame into mssql table

Question

engine = sqlalchemy.create_engine('mssql+pyodbc://<Username>:<password>@<DBServername>/<DBName>', pool_pre_ping=True)

I have data to be loaded into a DataFrame, by using connection string when i loop through each row in DF i am able to insert data into the table(1 row at a time using an INSERT INTO.. statement) but i want to use <DataFrame>.to_sql("<Table_name>", engine, if_exists='append')

Please help me understand if i am missing anything?

but i keep getting this error

/Library/Python/2.7/site-packages/sqlalchemy/connectors/pyodbc.py:79: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
  "No driver name specified; "
Traceback (most recent call last):
  File "/Users/snaidu/renuka/python-projects/staging/test.py", line 131, in <module>
    print(loaddmvcsvfile())
  File "/Users/snaidu/renuka/python-projects/staging/test.py", line 115, in loaddmvcsvfile
    result.to_sql("table1", engine, if_exists='append')
  File "/Library/Python/2.7/site-packages/pandas/core/generic.py", line 1534, in to_sql
    chunksize=chunksize, dtype=dtype)

Which python version you used to write this code? – Dinusha Thilakarathna Aug 16 '18 at 08:57 — Dinusha Thilakarathna, Aug 16 '18 at 08:57

score 0 · Answer 1 · answered May 05 '18 at 08:42

You need to specify the SQL driver which you want to use, for MS-SQL, this will be the native driver SQL Server Native Client 11.0

So for example you could use something like this:

cxn_string = 'mssql+pyodbc://<Username>:<password>@<DBServername>/<DBName>?driver=SQL+Server+Native+Client+11.0'
engine = sqlalchemy.create_engine(cxn_string, pool_pre_ping=True)

But that is a little long, and hard to read, so I typically make a helper function, something like this

import sqlalchemy
import urllib

def sql_connect(server, username, password, database, **kwargs):
'''Connect to SQL server'''
parameters = {'driver': 'SQL Server Native Client 11.0'}

string = r'mssql+pyodbc://{username}:{password}@{server}/{database}?{parameters}'
cxn_string = string.format(server=server,
                           username=username,
                           password=password,
                           database=database,
                           parameters=urllib.parse.urlencode(parameters))
return sqlalchemy.create_engine(cxn_string, **kwargs)

How to load data from dataFrame into mssql table

1 Answers1