Python pyodbc 和 mySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3982174/
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
pyodbc and mySQL
提问by skyeagle
I am unable to connect to mySQl db using pyodbc.
我无法使用 pyodbc 连接到 mySQl db。
Here is a snippet of my script:
这是我的脚本的一个片段：
import pyodbc
import csv
cnxn = pyodbc.connect("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;DATABASE=mydb; UID=root; PASSWORD=thatwouldbetelling;")
crsr = cnxn.cursor()
with open('C:\skunkworks\archive\data\myfile.csv','r') as myfile:
rows = csv.reader(myfile, delimiter=',', quotechar='"')
for row in rows:
insert_str = 'INSERT into raw_data VALUES(something, something)'
print insert_str
#crsr.execute(insert_str)
cnxn.commit()
myfile.close()
I get this error at the pyodbc.connect() line:
我在 pyodbc.connect() 行收到此错误：
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnectW)')
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] 未找到数据源名称且未指定默认驱动程序 (0) (SQLDriverConnectW)')
I have another question regarding this error (and Python scripts in general). When I run this as a script, it fails silently (I was expecting a stack trace). I have to type each line in manually to find where the error occured.
我有另一个关于此错误（以及一般的 Python 脚本）的问题。当我将它作为脚本运行时，它会默默地失败（我期待堆栈跟踪）。我必须手动输入每一行才能找到错误发生的位置。
I am being a bit lazy for now (no exception handling) - is this normal behaviour of a Python script without exception handling to fail silently?
我现在有点懒惰（没有异常处理） - 这是没有异常处理的 Python 脚本的正常行为是否会静默失败？
[Edit]
[编辑]
I am not using mysqldb because I am already using pyodbc to extract my data from another source (MS Access). Ok, not a good reason - but I am already grappling with pyodbc and I dont really fancy having to wrestle with another library/module/package(whatever its called in Python) for a "one off" job. I just want to move my data of from various data sources in the Windows environment to mySQl on Linux. once on Linux, I'll be back on terra firma.
我没有使用 mysqldb，因为我已经在使用 pyodbc 从另一个来源（MS Access）中提取我的数据。好吧，这不是一个很好的理由 - 但我已经在与 pyodbc 作斗争，我真的不喜欢为了“一次性”工作而不得不与另一个库/模块/包（无论它在 Python 中调用什么）搏斗。我只想将我的数据从 Windows 环境中的各种数据源移动到 Linux 上的 mySQl。一旦在 Linux 上，我将回到 terrafirma。
That is the entire 'script' right there. I just saved the code above into a file with a .py extension, and I run python myscript.py at the command line. I am running this on my XP machine
这就是整个“脚本”。我只是将上面的代码保存到一个扩展名为 .py 的文件中，然后在命令行运行 python myscript.py。我在我的 XP 机器上运行这个
回答by Ignacio Vazquez-Abrams
MySQLdb (or oursql) and pyodbc both have the same interface (DB-API 2), only you don't have to deal with ODBC's issues if you use the former. I strongly recommend you consider using MySQLdb (or oursql) instead.
MySQLdb（或oursql）和pyodbc 都具有相同的接口（DB-API 2），只是如果使用前者，则不必处理ODBC 的问题。我强烈建议您考虑使用 MySQLdb（或 oursql）。
回答by Mark
First, According to the official docs, if you want to connect without creating a DSN, you need to specify OPTION=3 in the connection string:
首先，根据官方文档，如果你想在不创建DSN的情况下进行连接，你需要在连接字符串中指定OPTION=3：
ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=venu;PASSWORD=venu;OPTION=3;"
If that fails to work, I'd further troubleshoot by creating a DSN.
如果这不起作用，我将通过创建 DSN 进一步排除故障。
Second, no Python should not be failing silently. If that is the case when you run your script, there is something else amiss.
其次，没有 Python 不应该默默地失败。如果在运行脚本时出现这种情况，则说明还有其他问题。
回答by rChavz
only need install mysql-connector-odbc-3.51.28-win32.msi.
只需要安装mysql-connector-odbc-3.51.28-win32.msi。
and pyodbc-2.1.7.win32-py2.7.exe.
和 pyodbc-2.1.7.win32-py2.7.exe。
of course, you have ready installed MySQL and python 2.7.
当然，你已经准备好安装 MySQL 和 python 2.7。
example:
例子：
import pyodbc
cndBase = pyodbc.connect("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; PORT=3306;DATABASE=nameDBase; UID=root; PASSWORD=12345;")
ptdBase = cndBase.cursor()
query_str = 'SELECT* FROM nameTabla;'
rows = ptdBase.execute(query_str)
for rw in rows:
print list(rw)`enter code here`
回答by Alejandro
I had this same mistake so I went over all the version I was using for the connection. This is what I found out:
我有同样的错误，所以我检查了我用于连接的所有版本。这是我发现的：
For Python 2.7 32 bits: - pyodbc must be 32bits - the DB Driver must be 32bits. (Microsoft Access should be 32 bits too)
对于 Python 2.7 32 位： - pyodbc 必须是 32 位 - DB 驱动程序必须是 32 位。（Microsoft Access 也应该是 32 位的）
For those who use the 64 bits version. You should check that everything is 64 bits too.
对于那些使用 64 位版本的人。您也应该检查所有内容是否也是 64 位。
In my case I was trying to connecto to an Oracle DB and Microsoft Access DB so I had to make the following components match the architechture version:
就我而言，我试图连接到 Oracle DB 和 Microsoft Access DB，因此我必须使以下组件与架构版本匹配：
- pyodbc (MS Access)
- python
- cx_Oracle (Oracle dialect for SQLalchemy)
- Oracle instantclient basic (Oracle. Do not forget to create the environment variable)
- py2exe (Making the excecutable app)
- pyodbc (MS Access)
- Python
- cx_Oracle（用于 SQLalchemy 的 Oracle 方言）
- Oracle Instantclient 基础（Oracle。不要忘记创建环境变量）
- py2exe（制作可执行的应用程序）
回答by xinyuan wang
Is that your driver name right?
你的司机名字对吗？
You can check your driver name inWindows -> Control panel -> System and security -> Administrative tools -> ODBC Data Sources -> Driver tabthen copy the river name to the first parameter
您可以在Windows -> 控制面板 -> 系统和安全 -> 管理工具 -> ODBC 数据源 -> 驱动程序选项卡中检查您的驱动程序名称，然后将河流名称复制到第一个参数
like
喜欢
cnxn = pyodbc.connect("DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=localhost;DATABASE=books; UID=root; PASSWORD=password;")
And my problem solved
我的问题解决了
or you may not install the driver and the step is simple.
或者你可能没有安装驱动程序，步骤很简单。
回答by Dhruv Kadia
I was getting the same error. It seemed the driver i was using to make the connection was not the driver installed in my system. Type ODBC on windows run and select ODBC Data Source(32/64) based on where you have installed the driver. From there click on System DSN and click add. From there you can see the MySQL driver installed in your system. Use the ANSI driver in your code where you are making the connection.
我遇到了同样的错误。我用来建立连接的驱动程序似乎不是我系统中安装的驱动程序。在 Windows 运行中键入 ODBC 并根据驱动程序的安装位置选择 ODBC 数据源（32/64）。从那里单击系统 DSN，然后单击添加。从那里您可以看到系统中安装的 MySQL 驱动程序。在进行连接的代码中使用 ANSI 驱动程序。
