"No driver name specified" writing pandas data frame into SQL Server table

Question

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; "

So there is no way of dumping dataframe into SQL Server's table? — user1700890, Nov 03 '16 at 16:17
Can you provide your versions: MS SQL Server version, Pandas version, SQLAlchemy version? — MaxU - stand with Ukraine, Feb 03 '17 at 13:36
@user1700890 see my suggestion in answer below, let me know if it works — denfromufa, Feb 06 '17 at 04:28
Try `df.to_sql('Test', engine.connect(), if_exists='append', index=False)` — Gord Thompson, Feb 06 '17 at 18:29
@GordThompson since pandas 0.14 this is not correct thing to do: http://stackoverflow.com/a/26766205/2230844 — denfromufa, Feb 07 '17 at 00:19
@user1700890, please see [cco's answer](http://stackoverflow.com/a/42081035/5741205) - i think it answers your question completely — MaxU - stand with Ukraine, Feb 07 '17 at 21:53

cco · Accepted Answer · 2022-02-19T00:22:35.553

32

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.

edited Feb 19 '22 at 00:22

answered Feb 07 '17 at 03:10

cco

5,873
1
16
21

According to this answer and sqlalchemy docs the driver is required ONLY when custom: http://stackoverflow.com/a/25662997/2230844 – denfromufa Feb 07 '17 at 03:30
4

Driver is required if you're not using a DSN - see the last error message in the question. – cco Feb 07 '17 at 03:45
@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? – user1700890 Feb 08 '17 at 02:31
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'. – cco Feb 08 '17 at 02:52
@cco, thank you again! By dictionary I mean something like this: `cnxn = connect(driver='{SQL Server}', server='localhost', database='test', uid='me', pwd='me2')` – user1700890 Feb 08 '17 at 16:53
2

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. – user1718097 Mar 12 '20 at 16:10
thanks this worked for me : engine = create_engine(f"mssql+pyodbc://{username}:{password}@{host}:{port}/{database}?driver=ODBC+Driver+17+for+SQL+Server") – Pedro Henrique Nov 10 '22 at 17:31

denfromufa · Answer 2 · 2017-02-08T04:27:55.407

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

edited Feb 08 '17 at 04:27

answered Feb 06 '17 at 04:27

denfromufa

5,610
13
81
138

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; "` – user1700890 Feb 07 '17 at 01:34

score 2 · Answer 3 · answered Sep 04 '20 at 07:52

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'

score 1 · Answer 4 · answered Feb 02 '21 at 15:27

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)

"No driver name specified" writing pandas data frame into SQL Server table

4 Answers4