如何在Windows上的Python 3中连接到MySQL?

问题:如何在Windows上的Python 3中连接到MySQL?

我在Windows上使用ActiveState Python 3,并想连接到我的MySQL数据库。我听说这mysqldb是要使用的模块。我找不到mysqldbPython 3。

有二进制文件存在的存储库mysqldb吗?如何在Windows的Python 3中连接到MySQL?

I am using ActiveState Python 3 on Windows and wanted to connect to my MySQL database. I heard that mysqldb was the module to use. I can’t find mysqldb for Python 3.

Is there a repository available where the binaries exist for mysqldb? How can I connect to MySQL in Python 3 on Windows?


回答 0

当前有一些将python 3与mysql结合使用的选项:

https://pypi.python.org/pypi/mysql-connector-python

  • 由Oracle官方支持
  • 纯Python
  • 有点慢
  • 与MySQLdb不兼容

https://pypi.python.org/pypi/pymysql

  • 纯Python
  • 比mysql-connector快
  • MySQLdb调用后几乎与完全兼容pymysql.install_as_MySQLdb()

https://pypi.python.org/pypi/cymysql

  • pymysql的fork与可选的C加速

https://pypi.python.org/pypi/mysqlclient

  • Django推荐的库。
  • 原始MySQLdb的友好分支,希望有一天能合并
  • 最快的实现,因为它基于C。
  • 与MySQLdb最兼容,因为它是一个fork
  • Debian和Ubuntu使用它来提供python-mysqldbpython3-mysqldb软件包。

此处的基准测试:https//github.com/methane/mysql-driver-benchmarks

There are currently a few options for using Python 3 with mysql:

https://pypi.python.org/pypi/mysql-connector-python

  • Officially supported by Oracle
  • Pure python
  • A little slow
  • Not compatible with MySQLdb

https://pypi.python.org/pypi/pymysql

  • Pure python
  • Faster than mysql-connector
  • Almost completely compatible with MySQLdb, after calling pymysql.install_as_MySQLdb()

https://pypi.python.org/pypi/cymysql

  • fork of pymysql with optional C speedups

https://pypi.python.org/pypi/mysqlclient

  • Django’s recommended library.
  • Friendly fork of the original MySQLdb, hopes to merge back some day
  • The fastest implementation, as it is C based.
  • The most compatible with MySQLdb, as it is a fork
  • Debian and Ubuntu use it to provide both python-mysqldb andpython3-mysqldb packages.

benchmarks here: https://github.com/methane/mysql-driver-benchmarks


回答 1

您可能应该使用pymysql-纯Python MySQL客户端
它与Python 3.x兼容,并且没有任何依赖关系。

这个纯Python MySQL客户端通过二进制客户端/服务器协议直接与服务器通信,从而为MySQL数据库提供DB-API。

例:

import pymysql
conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock', user='root', passwd=None, db='mysql')
cur = conn.cursor()
cur.execute("SELECT Host,User FROM user")
for r in cur:
    print(r)
cur.close()
conn.close()

You should probably use pymysql – Pure Python MySQL client instead.
It works with Python 3.x, and doesn’t have any dependencies.

This pure Python MySQL client provides a DB-API to a MySQL database by talking directly to the server via the binary client/server protocol.

Example:

import pymysql
conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock', user='root', passwd=None, db='mysql')
cur = conn.cursor()
cur.execute("SELECT Host,User FROM user")
for r in cur:
    print(r)
cur.close()
conn.close()

回答 2

我也尝试使用pymysql(在Win7 x64计算机上,Python 3.3),但运气不佳。我下载了.tar.gz,解压缩并运行“ setup.py install”,一切似乎都很好。直到我尝试连接到数据库并得到“ KeyError [56]”。我找不到任何地方记录的错误。

因此,我放弃了pymysql,而选择了Oracle MySQL连接器

它作为安装程序包提供,开箱即用。而且似乎也有据可查。

I also tried using pymysql (on my Win7 x64 machine, Python 3.3), without too much luck. I downloaded the .tar.gz, extract, ran “setup.py install”, and everything seemed fine. Until I tried connecting to a database, and got “KeyError [56]”. An error which I was unable to find documented anywhere.

So I gave up on pymysql, and I settled on the Oracle MySQL connector.

It comes as a setup package, and works out of the box. And it also seems decently documented.


回答 3

如果要先使用MySQLdb,则必须在Windows上键入cmd,在PC上安装pymysql

    pip install pymysql

然后在python shell中键入

    import pymysql
    pymysql.install_as_MySQLdb()
    import MySQLdb
    db = MySQLdb.connect("localhost" , "root" , "password")

这将建立连接。

if you want to use MySQLdb first you have to install pymysql on your pc by typing in cmd of windows

    pip install pymysql

then in python shell, type

    import pymysql
    pymysql.install_as_MySQLdb()
    import MySQLdb
    db = MySQLdb.connect("localhost" , "root" , "password")

this will establish the connection.


回答 4

未经测试,但在以下位置有一些可用的二进制文件:

非官方Windows二进制文件

Untested, but there are some binaries available at:

Unofficial Windows Binaries


回答 5

摘要

Mysqlclient是最佳选择(IMHO),因为它可以与Python 3+完美 协作,遵循预期的约定(与mysql连接器不同),使用对象名称mysqldb可以方便地移植现有软件,并且由Django用于python 3构建

mysqldb的二进制文件存在的地方有可用的存储库吗?

是。 mysqlclient允许您使用mysqldb函数。虽然,请记住,这不是 mysqldb 的直接端口,而是mysqlclient的构建

如何在Windows上的Python 3中连接到MySQL?

pip安装mysqlclient

#!/Python36/python
#Please change above path to suit your platform.  Am running it on Windows
import MySQLdb
db = MySQLdb.connect(user="my-username",passwd="my-password",host="localhost",db="my-databasename")
cursor = db.cursor()
cursor.execute("SELECT * from my-table-name")
data=cursor.fetchall()
for row in data :
    print (row)
db.close()

我找不到适用于Python 3的mysqldb。

mysqldb尚未移植

Summary

Mysqlclient is the best alternative(IMHO) because it works flawlessly with Python 3+, follows expected conventions (unlike mysql connector), uses the object name mysqldb which enables convenient porting of existing software and is used by Django for Python 3 builds

Is there a repository available where the binaries exist for mysqldb?

Yes. mysqlclient allows you to use mysqldb functions. Though, remember this is not a direct port by mysqldb, but a build by mysqlclient

How can I connect to MySQL in Python 3 on Windows?

pip install mysqlclient

Example

#!/Python36/python
#Please change above path to suit your platform.  Am running it on Windows
import MySQLdb
db = MySQLdb.connect(user="my-username",passwd="my-password",host="localhost",db="my-databasename")
cursor = db.cursor()
cursor.execute("SELECT * from my-table-name")
data=cursor.fetchall()
for row in data :
    print (row)
db.close()

I can’t find mysqldb for Python 3.

mysqldb has not been ported yet


回答 6

PyMySQL也提供类似MySQLDb的界面。您可以尝试进行初始化:

import pymysql
pymysql.install_as_MySQLdb()

github上还有一个用于python3的mysql-python端口。

https://github.com/davispuh/MySQL-for-Python-3

PyMySQL gives MySQLDb like interface as well. You could try in your initialization:

import pymysql
pymysql.install_as_MySQLdb()

Also there is a port of mysql-python on github for python3.

https://github.com/davispuh/MySQL-for-Python-3


回答 7

Oracle / MySQL提供了一个官方的纯Python DBAPI驱动程序:http ://dev.mysql.com/downloads/connector/python/

我在Python 3.3中使用了它,并发现它运行良好。还可以与SQLAlchemy一起使用。

另请参阅以下问题:登上Python 3火车是否还为时过早?

Oracle/MySQL provides an official, pure Python DBAPI driver: http://dev.mysql.com/downloads/connector/python/

I have used it with Python 3.3 and found it to work great. Also works with SQLAlchemy.

See also this question: Is it still too early to hop aboard the Python 3 train?


回答 8

在我的Mac OS X上,我尝试这样做:

之后,输入python3解释器并输入:

  1. 导入pymysql。如果没有错误,则表示安装正常。为了验证,编写一个脚本以这种形式连接到mysql:

  2. #一个用于MySQL连接的简单脚本import pymysql db = pymysql.connect(host =“ localhost”,user =“ root”,passwd =“* “,db =” biblioteca“)#当然,这是我的数据库的信息#关闭连接db.close()*

给它起一个名字(例如“ con.py”)并将其保存在桌面上。在终端中,键入“ cd desktop”,然后输入$ pythoncon.py。如果没有错误,则说明您已连接MySQL服务器。祝好运!

On my mac os maverick i try this:

After that, enter in the python3 interpreter and type:

  1. import pymysql. If there is no error your installation is ok. For verification write a script to connect to mysql with this form:

  2. # a simple script for MySQL connection import pymysql db = pymysql.connect(host=”localhost”, user=”root”, passwd=”*”, db=”biblioteca”) #Sure, this is information for my db # close the connection db.close ()*

Give it a name (“con.py” for example) and save it on desktop. In Terminal type “cd desktop” and then $python con.py If there is no error, you are connected with MySQL server. Good luck!


回答 9

CyMySQL https://github.com/nakagami/CyMySQL

我已经在Windows 7上安装了pip,使用python 3.3只是pip install cymysql

(您不需要赛顿)快速而无痛

CyMySQL https://github.com/nakagami/CyMySQL

I have installed pip on my windows 7, with python 3.3 just pip install cymysql

(you don’t need cython) quick and painless


回答 10

这并不能完全回答我最初的问题,但我认为让所有人知道我的工作和原因很重要。

由于网络上普遍存在2.7示例和模块,因此我选择继续使用python 2.7而不是python 3。

现在,我同时使用mysqldb和mysql.connector连接到Python 2.7中的MySQL。两者都很棒并且运作良好。我认为mysql.connector从长远来看最终会更好。

This does not fully answer my original question, but I think it is important to let everyone know what I did and why.

I chose to continue using python 2.7 instead of python 3 because of the prevalence of 2.7 examples and modules on the web in general.

I now use both mysqldb and mysql.connector to connect to MySQL in Python 2.7. Both are great and work well. I think mysql.connector is ultimately better long term however.


回答 11

我在树莓派上通过python3使用cymysql,我只需通过以下方式安装:sudo pip3 install cython sudo pip3 install cymysql在没有cython的情况下,但应使cymysql更快

到目前为止,它的工作原理很吸引人,与MySQLdb非常相似

I’m using cymysql with python3 on a raspberry pi I simply installed by: sudo pip3 install cython sudo pip3 install cymysql where cython is not necessary but should make cymysql faster

So far it works like a charm and very similar to MySQLdb


回答 12

这是一个有关如何使Python 3.7与Mysql一起使用的快速教程,
感谢所有得到我问题解答的人
-希望有一天能对您有所帮助。
————————————————– –
我的系统:
Windows版本:Pro 64位

要求..下载并安装这些第一…
1.下载XAMPP ..
https://www.apachefriends.org/download.html
2.下载的Python
https://www.python.org/downloads/windows/

– ————
//方法
————–
安装完成后,请先安装xampp-安装Python 3.7。
完成两者的安装后-重新启动Windows系统。
现在启动xampp并从控制面板启动mysql服务器。
通过打开CMD并在终端类型中确认版本

c:\>cd c:\xampp\mysql\bin

c:\xampp\mysql\bin>mysql -h localhost -v
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.21-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

这是检查MYSQL版本

c:\xampp\mysql\bin>python
Python 3.7.0b3 (v3.7.0b3:4e7efa9c6f, Mar 29 2018, 18:42:04) [MSC v.1913 64 bit (AMD64)] on win32

这是要检查Python版本,
既然都已确认,请在CMD中输入以下内容…

c:\xampp\mysql\bin>pip install pymysql

pymysql安装完成后。
在桌面上或其他任何地方创建一个名为“ testconn.py”的新文件以进行快速访问。
使用sublime或其他文本编辑器打开此文件,并将其放入其中。
切记更改设置以反映您的数据库。

#!/usr/bin/python
import pymysql
pymysql.install_as_MySQLdb() 

import MySQLdb
db = MySQLdb.connect(user="yourusernamehere",passwd="yourpasswordhere",host="yourhosthere",db="yourdatabasehere")
cursor = db.cursor()
cursor.execute("SELECT * from yourmysqltablehere")
data=cursor.fetchall()
for row in data :
    print (row)
db.close()

现在在您的CMD中-输入

c:\Desktop>testconn.py

就是这样…您现在已从python脚本完全连接到mysql …
享受…

This is a quick tutorial on how to get Python 3.7 working with Mysql
Thanks to all from who I got answers to my questions
– hope this helps somebody someday.
—————————————————-
My System:
Windows Version: Pro 64-bit

REQUIREMENTS.. download and install these first…
1. Download Xampp..
https://www.apachefriends.org/download.html
2. Download Python
https://www.python.org/downloads/windows/

————–
//METHOD
————–
Install xampp first after finished installing – install Python 3.7.
Once finished installing both – reboot your windows system.
Now start xampp and from the control panel – start the mysql server.
Confirm the versions by opening up CMD and in the terminal type

c:\>cd c:\xampp\mysql\bin

c:\xampp\mysql\bin>mysql -h localhost -v
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.21-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

This is to check the MYSQL version

c:\xampp\mysql\bin>python
Python 3.7.0b3 (v3.7.0b3:4e7efa9c6f, Mar 29 2018, 18:42:04) [MSC v.1913 64 bit (AMD64)] on win32

This is to check the Python version
Now that both have been confirmed type the following into the CMD…

c:\xampp\mysql\bin>pip install pymysql

After the install of pymysql is completed.
create a new file called “testconn.py” on your desktop or whereever for quick access.
Open this file with sublime or another text editor and put this into it.
Remember to change the settings to reflect your database.

#!/usr/bin/python
import pymysql
pymysql.install_as_MySQLdb() 

import MySQLdb
db = MySQLdb.connect(user="yourusernamehere",passwd="yourpasswordhere",host="yourhosthere",db="yourdatabasehere")
cursor = db.cursor()
cursor.execute("SELECT * from yourmysqltablehere")
data=cursor.fetchall()
for row in data :
    print (row)
db.close()

Now in your CMD – type

c:\Desktop>testconn.py

And thats it… your now fully connected from a python script to mysql…
Enjoy…


回答 13

导入pymysql

打开数据库连接

db = pymysql.connect(“ localhost”,“ root”,“”,“装饰品”)

使用cursor()方法准备一个游标对象

cursor = db.cursor()

sql =“ SELECT * FROM项目”

cursor.execute(sql)

获取列表列表中的所有行。

结果=结果的cursor.fetchall():item_title = row [1]注释= row [2]打印(“项目标题为以下=%s,注释为以下=%s”%\(item_title,评论))

import pymysql

open database connection

db = pymysql.connect(“localhost”,”root”,””,”ornament”)

prepare a cursor object using cursor() method

cursor = db.cursor()

sql = “SELECT * FROM item”

cursor.execute(sql)

Fetch all the rows in a list of lists.

results = cursor.fetchall() for row in results: item_title = row[1] comment = row[2] print (“Title of items are the following = %s,Comments are the following = %s” % \ (item_title, comment))