18

I am trying to write a Pandas' DataFrame into an SQL Server table. Here is my example:

import pyodbc import pandas as pd import sqlalchemy df = pd.DataFrame({'MDN': [242342342] }) engine = sqlalchemy.create_engine('mssql://localhost/Sandbox?trusted_connection=yes') df.to_sql('Test',engine, if_exists = 'append',index = False)

I am getting the following error message. Any thoughts on how to fix?

c:\python34\lib\site-packages\sqlalchemy\connectors\pyodbc.py:82: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections "No driver name specified; " --------------------------------------------------------------------------- AttributeError Traceback (most recent call last) <ipython-input-25-78677a18ce2d> in <module>() 4 engine = sqlalchemy.create_engine('mssql://localhost/Sandbox?trusted_connection=yes') 5 ----> 6 df.to_sql('Test',engine, if_exists = 'append',index = False) 7 8 #cnxn.close() c:\python34\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype) 980 self, name, con, flavor=flavor, schema=schema, if_exists=if_exists, 981 index=index, index_label=index_label, chunksize=chunksize, --> 982 dtype=dtype) 983 984 def to_pickle(self, path): c:\python34\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype) 547 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index, 548 index_label=index_label, schema=schema, --> 549 chunksize=chunksize, dtype=dtype) 550 551 c:\python34\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype) 1564 if_exists=if_exists, index_label=index_label, 1565 dtype=dtype) -> 1566 table.create() 1567 table.insert(chunksize) 1568 c:\python34\lib\site-packages\pandas\io\sql.py in create(self) 646 647 def create(self): --> 648 if self.exists(): 649 if self.if_exists == 'fail': 650 raise ValueError("Table '%s' already exists." % self.name) c:\python34\lib\site-packages\pandas\io\sql.py in exists(self) 634 635 def exists(self): --> 636 return self.pd_sql.has_table(self.name, self.schema) 637 638 def sql_schema(self): c:\python34\lib\site-packages\pandas\io\sql.py in has_table(self, name, schema) 1577 query = flavor_map.get(self.flavor) 1578 -> 1579 return len(self.execute(query, [name,]).fetchall()) > 0 1580 1581 def get_table(self, table_name, schema=None): c:\python34\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs) 1465 cur = self.con 1466 else: -> 1467 cur = self.con.cursor() 1468 try: 1469 if kwargs: AttributeError: 'Engine' object has no attribute 'cursor'

Also, is there ways to write connection string for create_engine differently? I would love to write it in form of a dictionary rather than a string.

Update: Here is my new environment:

MS SQL Server: Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )

Python: 3.4.3 (v3.4.3:9b73f1c3e601, Feb 24 2015, 22:43:06) [MSC v.1600 32 bit (Intel)]

Pandas version: '0.16.2'

sqlalchemy version: 1.1.3

Jupyter server version : 4.2.3

Now the line

engine = sqlalchemy.create_engine('mssql+pyodbc://localhost/Sandbox?trusted_connection=yes')

generates the following error: 

c:\python34\lib\site-packages\sqlalchemy\connectors\pyodbc.py:82: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections "No driver name specified; "
Improve this question
7
  • So there is no way of dumping dataframe into SQL Server's table? Commented Nov 3, 2016 at 16:17
  • 3
    Can you provide your versions: MS SQL Server version, Pandas version, SQLAlchemy version? Commented Feb 3, 2017 at 13:36
  • 1
    Try df.to_sql('Test', engine.connect(), if_exists='append', index=False) Commented Feb 6, 2017 at 18:29
  • 2
    @GordThompson since pandas 0.14 this is not correct thing to do: stackoverflow.com/a/26766205/2230844 Commented Feb 7, 2017 at 0:19
  • 1
    @user1700890, please see cco's answer - i think it answers your question completely Commented Feb 7, 2017 at 21:53

4 Answers 4

Reset to default
35
+50

You need to specify both that you want to use ODBC and what ODBC driver to use.

engine = sqlalchemy.create_engine('mssql+pyodbc://localhost/Sandbox?driver=SQL+Server+Native+Client+11.0')

Trusted connections are the default, so you don't need to specify that, although it shouldn't hurt to do so.

Update:
2022-02-18: The latest ODBC driver for SQL Server seems to be "ODBC Driver 17 for SQL Server". The driver named "SQL Server" is old and should not be used.
@user1718097 gives the useful suggestion of using [x for x in pyodbc.drivers()] to list the installed drivers. You can also list the installed drivers with the Get-OdbcDriver cmdlet in powershell.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

According to this answer and sqlalchemy docs the driver is required ONLY when custom: stackoverflow.com/a/25662997/2230844
Driver is required if you're not using a DSN - see the last error message in the question.
@cco Thank you very much for the answer! Is it possible to write it in dictinary format. Also in case I need to provide login and password, how can I do it?
Changing localhost in the string to user:password@localhost should do the right thing. If that doesn't work, appending ;User Id=myUsername; Password=myPassword; should do the trick. I'm not sure what you mean by 'dictionary format'.
When I tried this, I wasn't sure which OBDC drivers I had installed on my computer. After importing pyodbc, I ran [x for x in pyodbc.drivers()] to get a list of installed drivers. In my case, only ['SQL Server'] so I was able to include driver=SQL+Server and everything worked.
|
2

The likely problem is that you have not specified the driver, so try:

engine = sqlalchemy.create_engine('mssql+pyodbc://localhost/Sandbox?trusted_connection=yes')

This is based on the warning message that you got on the top:

c:\python34\lib\site-packages\sqlalchemy\connectors\pyodbc.py:82: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections "No driver name specified; "

Note that you can also use pymssql instead of pyodbc, but MS recommends the latter.

EDIT

Here is official documentation on how to connect with/without DSN (data source name):

https://github.com/mkleehammer/pyodbc/blob/master/docs/index.md#connect-to-a-database

Improve this answer

1 Comment

My enviroment slightly changed, but the original code and your suggestion, I am getting the same error c:\python34\lib\site-packages\sqlalchemy\connectors\pyodbc.py:82: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections "No driver name specified; "
2

I know the question has been answered for some time now and it's just a warning, but if you have transferred everything correctly and this error still occurs it's annoying.

For all those who had to struggle with it like I did, you can also enter the driver directly in the script, Pyodbc.py offers the possibility for this (row 26 - 28):

 # for non-DSN connections, this *may* be used to # hold the desired driver name pyodbc_driver_name = 'ODBC Driver 17 for SQL Server'
Improve this answer

Comments

1

Above information was much useful. Commenting below version of mine as consolidated which can help freshers during search.

#using library pandas and pyodbc - if not available please use pip install commands to install library based on version. Python version used here is 3.7.8

import pandas as pd from sqlalchemy import create_engine import pyodbc #This query will work for sql authentication def mssql_engine(): engine = create_engine('mssql+pyodbc://type_username:type_password@type_servername_or_localhostname/type_database_name?driver=SQL+Server+Native+Client+11.0') return engine #This query will for windows authentication #Note: Uncomment below code for windows authentication #def mssql_engine(): #engine = create_engine('mssql+pyodbc://localhostname/db_name?driver=SQL+Server+Native+Client+11.0') #return engine query = 'select * from table_name' #using pandas to read from sql and passing connection string as function df = pd.read_sql(query, mssql_engine() ) #printing result print(df)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.