How do I connect to a MySQL Database in Python?

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

[MySQLdb](

[To see links please register here]

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

[To see links please register here]

) instead. Here is a [query related](

[To see links please register here]

) tutorial, and here is a tutorial on [ORM capabilities](

[To see links please register here]

) of SQLALchemy.

For python 3.3

CyMySQL

[To see links please register here]

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

(you don't need cython)
quick and painless

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

[To see links please register here]

.

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',
host='127.0.0.1',
database='employees')

try:
cursor = cnx.cursor()
cursor.execute("""
select 3 from your_table
""")
result = cursor.fetchall()
print result
finally:
cnx.close()

Also take a look at [Storm][1]. 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()
user.name = u"Mark"

print str(user.ID) # None

store.add(user)
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, User.name == 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][2].


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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.

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',
user='mahdi',
password='mahdi',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS "+database)
connection.select_db(database)
sql_create = "CREATE TABLE IF NOT EXISTS "+tablename+(timestamp DATETIME NOT NULL PRIMARY KEY)"
cursor.execute(sql_create)
connection.commit()
cursor.close()

first install the driver

pip install MySQL-python

Then a basic code goes like this:

#!/usr/bin/python
import MySQLdb

try:
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
cur.execute(query_string)

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

# Close the connection
db.close()
except Exception, e:
print 'Error ', e

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

Use [pymysql][1]. 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.

**Installation**

`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',
user='user',
password='passwd',
db='db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)

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

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

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

> **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
pymysql.install_as_MySQLdb()

All subsequent references to MySQLdb will use pymysql transparently.

[1]:

[To see links please register here]

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


pip install mysqlclient

example code

import mysql.connector
import _mysql
db=_mysql.connect("127.0.0.1","root","umer","sys")
#db=_mysql.connect(host,user,password,db)
# Example of how to insert new values:
db.query("""INSERT INTO table1 VALUES ('01', 'myname')""")
db.store_result()
db.query("SELECT * FROM new1.table1 ;")
#new1 is scheme table1 is table mysql
res= db.store_result()
for i in range(res.num_rows()):
print(result.fetch_row())
see

[To see links please register here]