How to connect to a MySQL database from python?

Question

How do i connect to a MySQL database using a python program.

Answer 1

The MySQLdb package is the preferred method.

Answer 2

Here's one way to do it.

#!/usr/bin/python
import MySQLdb

# connect
db = MySQLdb.connect(host="localhost", user="appuser", passwd="",
db="onco")

cursor = db.cursor()

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

# get the number of rows in the resultset
numrows = int(cursor.rowcount)

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

From here.

Answer 3

Try using MySQLdb

There is a how to page here: http://www.kitebird.com/articles/pydbapi.html

---

From the page:

# server_version.py - 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 ()

Answer 4

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.

Answer 5

Connecting to MYSQL with Python in 3 steps

1 - Setting

You must install a mysql driver before doing anything. Like Java and unlike PHP, only generical DB management is installed by default with Python. The most used package to do so is MySQLdb.

For Windows user, you can get a simple exe : http://sourceforge.net/project/showfiles.php?group_id=22307

For Linux, this is a casual package (debian name it python-mysql).

2 - Usage

After installing, reboot. This is not mandatory, but will prevent me from answering 3 or 4 others questions in this post is something goes wrong. So please reboot.

Then it is just like using another package :

#!/usr/bin/python
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 query you need
cur = db.cursor() 

# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

# 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

For a big project, you may want to use an ORM. In that case, you can have a look to SQLAlchemy.