How do I connect to a MySQL Database in Python?

How do I connect to a MySQL database using a python program?

16.12.2008 21:49:09
Most answers here focus on installing MySQLdb library, I would really suggest opting for MySQL Connector/Python provided by MySQL/Oracle, which makes the process much simpler:…
Mr. Napik 6.01.2014 21:32:46
The problem with using Oracle's Connector/Python is that it has subtle bugs and other integration issues. It's easy to install, but nearly impossible to get to work for all the real-world use cases I've tried it for. Hence why I always recommend MySQLdb.
Joe C. 29.01.2015 17:06:08
@Mr.Napik I'm using pymysql because it's pure free Python according to this comparison.
Cees Timmerman 25.10.2016 18:36:53

Here's one way to do it, using MySQLdb, which only supports Python 2:

import MySQLdb

# Connect
db = MySQLdb.connect(host="localhost",

cursor = db.cursor()

# Execute SQL select statement
cursor.execute("SELECT * FROM location")

# Commit your changes if writing
# In this case, we are only reading data
# db.commit()

# Get the number of rows in the resultset
numrows = cursor.rowcount

# Get and display one row at a time
for x in range(0, numrows):
    row = cursor.fetchone()
    print row[0], "-->", row[1]

# Close the connection

Reference here

18.04.2019 09:45:02
is there a need to commit in this example as you are not modifying the database ?
sumanth232 10.02.2015 16:53:50
BEWARE - this module has no timezone support on connection
kommradHomer 26.02.2015 09:50:43
It's not working on my shared hosting. Saying that No module named MySQLdb. How can I use mysql with python on shared hosting. Any alternative ?
Bhavesh Gangani 25.04.2015 10:05:56
@BhaveshGangani you'll need to contact your host and ask why Python libraries they support. If they support pypi, then you could always load the package when your deployment happens.
George Stocker 25.04.2015 12:10:20
Interestingly even though the docs say this only works with python 2, it does seem to work with python 3 for me
lucidbrot 23.08.2019 20:05:17

Try using MySQLdb. MySQLdb only supports Python 2.

There is a how to page here:

From the page:

# - retrieve and display database server version

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()
18.04.2019 09:46:01
BEWARE - this module has no timezone support on connection
kommradHomer 26.02.2015 09:51:39
@kommradHomer Can you elaborate please?
Pyderman 17.02.2016 02:27:36
@kommradHomer - what does timezone support matter to you (or others - especially when connecting to localhost)?
warren 27.07.2016 18:49:32
@warren no, i wasn't connecting localhost in my case
kommradHomer 27.07.2016 19:17:17
How do I detect which library is installed for connecting to a database?
posfan12 7.06.2018 23:03:58

MySQLdb is the straightforward way. You get to execute SQL queries over a connection. Period.

My preferred way, which is also pythonic, is to use the mighty SQLAlchemy instead. Here is a query related tutorial, and here is a tutorial on ORM capabilities of SQLALchemy.

16.12.2008 22:29:06
The links are dead, can you look into that and update it with the correct link ?
Naveen Vijay 15.07.2014 08:20:12

Connecting to MYSQL with Python 2 in three steps

1 - Setting

You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it's hard to install it using easy_install. Please note MySQLdb only supports Python 2.

For Windows user, you can get an exe of MySQLdb.

For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb (for debian based distros), yum install MySQL-python (for rpm-based), or dnf install python-mysql (for modern fedora distro) in command line to download.)

For Mac, you can install MySQLdb using Macport.

2 - Usage

After installing, Reboot. This is not mandatory, But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.

Then it is just like using any other package :

import MySQLdb

db = MySQLdb.connect(host="localhost",    # your host, usually localhost
                     user="john",         # your username
                     passwd="megajonhy",  # your password
                     db="jonhydb")        # name of the data base

# you must create a Cursor object. It will let
#  you execute all the queries you need
cur = db.cursor()

# Use all the SQL you like

# print all the first cell of all the rows
for row in cur.fetchall():
    print row[0]


Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.

3 - More advanced usage

Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.

I strongly advise you to use it: your life is going to be much easier.

I recently discovered another jewel in the Python world: peewee. It's a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, Where using big tools like SQLAlchemy or Django is overkill :

import peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

book = Book(author="me", title='Peewee is cool')
for book in Book.filter(author="me"):
    print book.title

This example works out of the box. Nothing other than having peewee (pip install peewee) is required.

18.04.2019 09:43:27
Glad you like peewee!! I've added support for MySQL as well as some documentation on integrating with it. Happy hacking!
coleifer 31.10.2011 03:36:16
Note, as of writing this, MySQLdb does not support Python 3. The sourceforge page says "Python 3 support coming soon" but it has not been updated since 2012-10-08. For Python 3 there's PyMySQL and oursql.
paul 9.05.2013 03:07:20
Also note the following is required: pip install MySQL-python
Ben Crowhurst 6.09.2013 11:51:56
This answer would be more helpful with an example connection to an external server on a non-standard port to show how to do it. Saying localhost is the usual way to do it is nonsense.
Anthony 22.12.2013 14:49:47
Peewee has change the default behavior and does not use autocommit by default anymore. Even the DB driver situation has changed now. The question needs updating, but since the abuse of edits on some of my answers, I don't edit them anymore.
e-satis 1.06.2016 12:57:14

As a db driver, there is also oursql. Some of the reasons listed on that link, which say why oursql is better:

  • oursql has real parameterization, sending the SQL and data to MySQL completely separately.
  • oursql allows text or binary data to be streamed into the database and streamed out of the database, instead of requiring everything to be buffered in the client.
  • oursql can both insert rows lazily and fetch rows lazily.
  • oursql has unicode support on by default.
  • oursql supports python 2.4 through 2.7 without any deprecation warnings on 2.6+ (see PEP 218) and without completely failing on 2.7 (see PEP 328).
  • oursql runs natively on python 3.x.

So how to connect to mysql with oursql?

Very similar to mysqldb:

import oursql

db_connection = oursql.connect(host='',user='foo',passwd='foobar',db='db_name')
cur.execute("SELECT * FROM `tbl_name`")
for row in cur.fetchall():
    print row[0]

The tutorial in the documentation is pretty decent.

And of course for ORM SQLAlchemy is a good choice, as already mentioned in the other answers.

15.10.2012 21:25:05
I don't understand the 'runs natively on python 3.x'. I just tried to install it and the bombs out because it doesn't even use Python 3 compatible print calls. Sounded great until I actually tried it.
otakucode 12.10.2015 16:16:47

Oracle (MySQL) now supports a pure Python connector. That means no binaries to install: it's just a Python library. It's called "Connector/Python".

28.05.2013 15:39:22
is this faster than using mysqldb?
alwbtc 31.08.2013 21:20:09
Yes, it is. Also, it's less hassle than MySQLdb and the API is better in my opinion. This should be the answer.
Anthony 22.12.2013 14:44:41
using the official mysql connector of python is the best way to win time
Anas 17.04.2014 01:09:05
@J0hnG4lt You can just click on "No thanks, just start my download" below the login form (which actually seems mandatory, but isn't).
ComFreek 22.05.2015 10:28:06
@ComFreek great. How is that better? Can we stop applying the "Windows workflow". Package management exists.
J0hnG4lt 22.05.2015 10:51:30

For python 3.3


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

(you don't need cython) quick and painless

11.09.2013 10:04:37
You could create a new question on SO and comment with a link to it here.
Lazik 19.10.2013 17:16:53

If you do not need MySQLdb, but would accept any library, I would very, very much recommend MySQL Connector/Python from MySQL:

It is one package (around 110k), pure Python, so it is system independent, and dead simple to install. You just download, double-click, confirm license agreement and go. There is no need for Xcode, MacPorts, compiling, restarting …

Then you connect like:

import mysql.connector    
cnx = mysql.connector.connect(user='scott', password='tiger',

   cursor = cnx.cursor()
      select 3 from your_table
   result = cursor.fetchall()
   print result
5.12.2014 00:31:23
Not quite as simple as you claim. When I tried to install the rpm I got a couple of dependency issues (FileDigests and PayloadIsXz) from my RPM: rpm -i mysql-connector-python-1.1.5-1.el6.noarch.rpm error: Failed dependencies: rpmlib(FileDigests) <= 4.6.0-1 is needed by mysql-connector-python-1.1.5-1.el6.noarch rpmlib(PayloadIsXz) <= 5.2-1 is needed by mysql-connector-python-1.1.5-1.el6.noarch
tatlar 6.02.2014 18:43:37
I used to prefer this library, but it's no longer officially supported on PyPi. I've since moved to MySQLdb.
Steven Mercatante 10.09.2014 17:59:49
@Arms: no longer officially supported on PyPi? Do you have any source for this?
Messa 31.01.2015 16:09:02
pip install mysql-connector-python will also work. I don't see where it says no longer supported on PyPi? Great if you don't have access to the gcc/C compilers on your system, and therefore can't install mysqldb.
decvalts 7.08.2015 09:03:38
as of Nov 7, 2016, the package for pip is just mysql-connector, pip search mysql-connector to find the package name. answer from here:
nommer 8.11.2016 00:32:35

Also take a look at Storm. It is a simple SQL mapping tool which allows you to easily edit and create SQL entries without writing the queries.

Here is a simple example:

from storm.locals import *

# User will be the mapped object; you have to create the table before mapping it
class User(object):
        __storm_table__ = "user" # table name
        ID = Int(primary=True) #field ID
        name= Unicode() # field name

database = create_database("mysql://root:password@localhost:3306/databaseName")
store = Store(database)

user = User() = u"Mark"

print str(user.ID) # None

store.flush() # ID is AUTO_INCREMENT

print str(user.ID) # 1 (ID)

store.commit() # commit all changes to the database

To find and object use:

michael = store.find(User, == u"Michael").one()
print str(user.ID) # 10

Find with primary key:

print store.get(User, 1).name #Mark

For further information see the tutorial.

3.06.2015 12:28:05

Just a modification in above answer. Simply run this command to install mysql for python

sudo yum install MySQL-python
sudo apt-get install MySQL-python

remember! It is case sensitive.

21.09.2015 04:31:40
I installed MySQL-python via yum install. The installation was complete but I cannot import the MySQLdb module. It says no such module. Any idea why is it so?
user3527975 2.03.2016 05:57:37
It's not clear which answer you mean when you say "above answer". Please make your answer complete in itself. If you want to refer to other answers, please link to the specific answer.
Flimm 18.04.2019 09:47:08

Stop Using MySQLDb if you want to avoid installing mysql headers just to access mysql from python.

Use pymysql. It does all of what MySQLDb does, but it was implemented purely in Python with NO External Dependencies. This makes the installation process on all operating systems consistent and easy. pymysql is a drop in replacement for MySQLDb and IMHO there is no reason to ever use MySQLDb for anything... EVER! - PTSD from installing MySQLDb on Mac OSX and *Nix systems, but that's just me.


pip install pymysql

That's it... you are ready to play.

Example usage from pymysql Github repo

import pymysql.cursors
import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',

    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('',))
        result = cursor.fetchone()

ALSO - Replace MySQLdb in existing code quickly and transparently

If you have existing code that uses MySQLdb, you can easily replace it with pymysql using this simple process:

# import MySQLdb << Remove this line and replace with:
import pymysql

All subsequent references to MySQLdb will use pymysql transparently.

8.09.2017 19:56:52
"Always" is a little strong... What is wrong with peewee or sqlalchemy? Even if they do exist as ORM libraries, they are still just as useful
cricket_007 10.02.2016 01:30:05
No I don't think it's strong at all. Given the option to use either MySQLDb or PyMysql I think choosing PyMysql is a no brainer because it is a simple pip install with no dependencies, while MySQLDb requires mysql development header files to be installed for your OS platform for it to work. Sqlalchemy doesn't factor into this because it still needs to wrap around a module that can talk to mysql like PyMysql, MySQLDb, CyMySQL e.t.c. Peewee looks interesting though, I'll check that out
OkezieE 10.02.2016 15:01:22
@cricket_007 peewee and sqlalchemly don't implement their own db connector, they provide a layer above MySQLdb etc.
rocksportrocker 4.07.2017 07:44:53

Despite all answers above, in case you do not want to connect to a specific database upfront, for example, if you want to create the database still (!), you can use connection.select_db(database), as demonstrated in the following.

import pymysql.cursors
connection = pymysql.connect(host='localhost',
cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS "+database)
sql_create = "CREATE TABLE IF NOT EXISTS "+tablename+(timestamp DATETIME NOT NULL PRIMARY KEY)"
22.06.2016 14:18:47

first install the driver

pip install MySQL-python   

Then a basic code goes like this:

import MySQLdb

    db = MySQLdb.connect(host="localhost",      # db server, can be a remote one 
                     db="mydb"                  # database
                     user="mydb",               # username
                     passwd="mydb123",          # password for this username

    # Create a Cursor object
    cur = db.cursor()

    # Create a query string. It can contain variables
    query_string = "SELECT * FROM MY_TABLE"

    # Execute the query

    # Get all the rows present the database
    for each_row in cur.fetchall():
        print each_row

    # Close the connection
except Exception, e:
    print 'Error ', e 
21.08.2017 18:18:11

mysqlclient is the best as others only provide support to specific versions of python

 pip install mysqlclient

example code

    import mysql.connector
    import _mysql
    # Example of how to insert new values:
    db.query("""INSERT INTO table1 VALUES ('01', 'myname')""")
    db.query("SELECT * FROM new1.table1 ;") 
    #new1 is scheme table1 is table mysql 
    res= db.store_result()
    for i in range(res.num_rows()):


28.01.2018 14:35:33
+1 for proposing the fastest solution for python 3. However, mysql.connector and _mysql give both an import error (though the second option should work according to the documentation). import MySQLdb works, and then MySQLdb.connect...
Suzana 16.05.2019 12:59:10


SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. SQLAlchemy provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.


pip install sqlalchemy

RAW query

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

engine = create_engine("mysql://<user_name>:<password>@<host_name>/<db_name>")
session_obj = sessionmaker(bind=engine)
session = scoped_session(session_obj)

# insert into database
session.execute("insert into person values(2, 'random_name')")

ORM way

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

Base = declarative_base()
engine = create_engine("mysql://<user_name>:<password>@<host_name>/<db_name>")
session_obj = sessionmaker(bind=engine)
session = scoped_session(session_obj)

# Bind the engine to the metadata of the Base class so that the
# declaratives can be accessed through a DBSession instance
Base.metadata.bind = engine

class Person(Base):
    __tablename__ = 'person'
    # Here we define columns for the table person
    # Notice that each column is also a normal Python instance attribute.
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

# insert into database
person_obj = Person(id=12, name="name")
6.10.2018 16:46:14

First, install python-mysql connector from

on Python console enter:

pip install mysql-connector-python-rf
import mysql.connector
8.06.2018 15:16:41
The question was a little too broad, but this answer seems to be a bit too narrow (at least in the sense that installing and importing a module alone won't do). There also seems to be shell and python intermixed in single code block without any explanation or hints wrt what goes where and does what.
Ondrej K. 8.06.2018 15:37:39

you can connect your python code to mysql in this way.

import MySQLdb
db = MySQLdb.connect(host="localhost",

cursor = db.cursor()
14.07.2018 19:04:53

First install the driver (Ubuntu)

  • sudo apt-get install python-pip

  • sudo pip install -U pip

  • sudo apt-get install python-dev libmysqlclient-dev

  • sudo apt-get install MySQL-python

MySQL database connection codes

import MySQLdb
conn = MySQLdb.connect (host = "localhost",user = "root",passwd = "pass",db = "dbname")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()
5.08.2018 05:25:36

Best way to connect to MySQL from python is to Use MySQL Connector/Python because it is official Oracle driver for MySQL for working with Python and it works with both Python 3 and Python 2.

follow the steps mentioned below to connect MySQL

  1. install connector using pip

    pip install mysql-connector-python

or you can download the installer from

  1. Use connect() method of mysql connector python to connect to MySQL.pass the required argument to connect() method. i.e. Host, username, password, and database name.

  2. Create cursor object from connection object returned by connect()method to execute SQL queries.

  3. close the connection after your work completes.


import mysql.connector
 from mysql.connector import Error
     conn = mysql.connector.connect(host='hostname',
     if conn.is_connected():
       cursor = conn.cursor()
       cursor.execute("select database();")
       record = cursor.fetchall()
       print ("You're connected to - ", record)
 except Error as e :
    print ("Print your error msg", e)
    #closing database connection.

Reference -

Important API of MySQL Connector Python

  • For DML operations - Use cursor.execute() and cursor.executemany() to run query. and after this use connection.commit() to persist your changes to DB

  • To fetch data - Use cursor.execute() to run query and cursor.fetchall(), cursor.fetchone(), cursor.fetchmany(SIZE) to fetch data

18.03.2020 05:10:05
You're* connected to...
John Forbes 15.03.2020 21:27:02

for Python3.6 I found two driver: pymysql and mysqlclient. I tested the performance between them and got the result: the mysqlclient is faster.

below is my test process(need install python lib profilehooks to analyze time elapse:

raw sql: select * from FOO;

immediatly execute in mysql terminal: 46410 rows in set (0.10 sec)

pymysql (2.4s):

from profilehooks import profile
import pymysql.cursors
import pymysql
connection = pymysql.connect(host='localhost', user='root', db='foo')
c = connection.cursor()

def read_by_pymysql():
    c.execute("select * from FOO;")
    res = c.fetchall()


here's the pymysql profile: enter image description here

mysqlclient (0.4s)

from profilehooks import profile
import MySQLdb

connection = MySQLdb.connect(host='localhost', user='root', db='foo')
c = connection.cursor()

def read_by_mysqlclient():
    c.execute("select * from FOO;")
    res = c.fetchall()


here's the mysqlclient profile: enter image description here

So, it seems that mysqlclient is much faster than pymysql

18.08.2018 14:59:51

This is Mysql DB connection

from flask import Flask, render_template, request
from flask_mysqldb import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'root'
app.config['MYSQL_DB'] = 'MyDB'

mysql = MySQL(app)

@app.route('/', methods=['GET', 'POST']) 
def index():
    if request.method == "POST":
        details = request.form
        cur = mysql.connection.cursor()
        cur.execute ("_Your query_")
        return 'success'
    return render_template('index.html')

if __name__ == '__main__':
19.01.2019 10:14:31

Run this command in your terminal to install mysql connector:

pip install mysql-connector-python

And run this in your python editor to connect to MySQL:

import mysql.connector

mydb = mysql.connector.connect(

Samples to execute MySQL Commands (in your python edior):

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")    
mycursor.execute("SHOW TABLES")

mycursor.execute("INSERT INTO customers (name, address) VALUES ('John', 'Highway 21')")    
mydb.commit() # Use this command after insert or update

For more commands:

6.12.2019 15:42:48
Can you please answer this?? It is related to mysql-connector only.…
user11363434 19.12.2019 08:26:22
Some guys already answered your question there. You just forgot to run mydb.commit() after inserting values into table
Sherzod 19.12.2019 10:39:42

Even though some of you may mark this as a duplicate and get upset that I am copying someone else's answer, I would REALLY like to highlight an aspect of Mr. Napik's response. Because I missed this, I caused nationwide website downtime (9min). If only someone shared this information, I could have prevented it!

Here is his code:

import mysql.connector    
cnx = mysql.connector.connect(user='scott', password='tiger',
   cursor = cnx.cursor()
   cursor.execute("""select 3 from your_table""")
   result = cursor.fetchall()

The important thing here is the Try and Finally clause. This allows connections to ALWAYS be closed, regardless of what happens in the cursor/sqlstatement portion of the code. A lot of active connections cause DBLoadNoCPU to spike and could crash a db server.

I hope this warning helps to save servers and ultimately jobs! :D

28.02.2020 15:37:14