163

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?

Improve this question
3
  • Thanks to casevh for the link to the unofficial binaries, but I was too impatient and it appears that the masses are still using python2 ... so I installed python 2.7 and installed MySQLdb from codegood.com/archives/129 Commented Feb 10, 2011 at 18:27
  • 1
    possible duplicate of MySQL-db lib for Python 3.0 ? Commented Mar 13, 2011 at 9:43
  • 1
    Every time I type pip, my stomach churns. 7 years and counting. Commented Nov 15, 2018 at 17:03

15 Answers 15

Reset to default
298

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

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

13 Comments

pip install mysqlclient just worked for me! thanks for the comprehensive list of options.
mysqlclient just worked for my Python3.4+Django+Passenger+Dreamhost install.
Ubuntu has mysqlclient Debian not
Looks like mysqlclient is in debian testing as python3-mysqldb, but it's not in stable. So it will be in the next version.
for install mysql connector on ubuntu you can use a apt like sudo apt-get install python3-mysql.connector
|
73

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()
Improve this answer

8 Comments

unfortunately it's a ZERO-documented library which happen to just not work
@Lohoris: How come I'm using it without any problems?
I have not been able to install this on Windows 7 with Python 3.x. Anyone know how?
Python version 3.3.1 here and it doesn't install. Complains about missing module called constants.
@Pius Because you're doing it wrong. Just checked and it works.
|
11

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.

Improve this answer

Comments

7

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.

Improve this answer

1 Comment

I was using MySQLdb and that works great, but clearly, I would expect this to become the defacto standard over time.
6

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

Improve this answer

Comments

5

Untested, but there are some binaries available at:

Unofficial Windows Binaries

Improve this answer

Comments

5

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

Improve this answer

Comments

2

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?

Improve this answer

Comments

1

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!

Improve this answer

Comments

0

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

Improve this answer

Comments

0

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.

Improve this answer

3 Comments

I'm curious if you are still using Python 2.7. In general, I think that it's really important to make the transition to Python 3, and I've been surprised to see so many programmers opting for Python 2.7 for new projects. How did things work out?
@vy32 I think you might be less surprised by developers choosing py27 if you consider that many server OSes don't directly support Python 3. Red Hat Enterprise Linux was still being bundled with Python 2.6 until the most recent version, and even that one only has py27.
Another reason to use python 2.7 is because Maya (which supports python) uses 2.7 internally. BTW... if you also need to connect to MySQL through python in Maya... use the pure python mysql connector.
0

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

Improve this answer

Comments

0

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...

Improve this answer

Comments

0

follow these steps:

  1. import pymysql

  2. install

    python -m pip install PyMySQL

    python -m pip install PyMySQL[rsa]

    python -m pip install PyMySQL[ed25519]

finally try again to install pip install mysql-connector.

these steps solved my error

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
0

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))

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.