“未指定驱动程序名称”将 Pandas 数据框写入 SQL Server 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40332319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"No driver name specified" writing pandas data frame into SQL Server table
提问by user1700890
I am trying to write a Pandas' DataFrame into an SQL Server table. Here is my example:
我正在尝试将 Pandas 的 DataFrame 写入 SQL Server 表。这是我的例子：
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_enginedifferently? I would love to write it in form of a dictionary rather than a string.
另外，有没有办法以create_engine不同的方式编写连接字符串？我喜欢用字典而不是字符串的形式来写它。
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: )
MS SQL Server：Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 2012 年 2 月 10 日 19:39:15 版权所有 (c) Microsoft Corporation 标准版（64 位），Windows NT 6.2（内部版本 9200：）
Python: 3.4.3 (v3.4.3:9b73f1c3e601, Feb 24 2015, 22:43:06) [MSC v.1600 32 bit (Intel)]
Python：3.4.3（v3.4.3:9b73f1c3e601，2015 年 2 月 24 日，22:43:06）[MSC v.1600 32 位（英特尔）]
Pandas version: '0.16.2'
Pandas版本：'0.16.2'
sqlalchemy version: 1.1.3
sqlalchemy 版本：1.1.3
Jupyter server version : 4.2.3
Jupyter 服务器版本：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; "
回答by cco
You need to specify both that you want to use ODBC and what ODBC driver to use.
您需要同时指定要使用 ODBC 和要使用的 ODBC 驱动程序。
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.
受信任的连接是默认设置，因此您不需要指定它，尽管这样做不会有什么坏处。
回答by denfromufa
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.
请注意，您也可以使用 pymssql 代替 pyodbc，但 MS 建议使用后者。
EDIT
编辑
Here is official documentation on how to connect with/without DSN (data source name):
以下是有关如何使用/不使用 DSN（数据源名称）进行连接的官方文档：
https://github.com/mkleehammer/pyodbc/blob/master/docs/index.md#connect-to-a-database
https://github.com/mkleehammer/pyodbc/blob/master/docs/index.md#connect-to-a-database
