Simple Python-MySQL Bridge?

Question

Is there a nice and simple interface between Python and MySQL? I've looked at MySQLdb module, SQLAlchemy, and the module provided by MySQL. They work, but are just clunky and difficult to quickly work with. I'm brand new to Python, but I've done this in MATLAB and they have an incredibly easy interface. I.E.

Every time you want to perform a query in Python it seems like you have to do something like:

import datetime
import mysql.connector
cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
     "WHERE hire_date BETWEEN %s AND %s")
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)
cursor.execute(query, (hire_start, hire_end))
for (first_name, last_name, hire_date) in cursor:
print("{}, {} was hired on {:%d %b %Y}".format(
last_name, first_name, hire_date))
cursor.close()
cnx.close()

Whereas in MATLAB, I initiate the connection once (like when starting up the program, and then retrieving something is as simple as (from here):

[Fn,Ln,Hd] = mysql(['SELECT first_name, last_name, hire_date FROM employees WHERE hire_date = ',num2str(some_date)])

No cursors and connections to make every time you query, just a simple I/O query executer and data returner. I like playing with databases and have many cross-platform projects. Being able to instantly connect to and look at data in MATLAB is an awesome feature. Is there a Python bridge to do this?

You still have to supply the connection information for the MySQL database *somewhere*. — Amber, Oct 08 '12 at 03:38

score 4 · Accepted Answer · edited May 23 '17 at 12:06

4

Use Pandas. It has a great interface. Look here:

python-pandas and databases like mysql

I use it to access all my databases from python.

edited May 23 '17 at 12:06

Community

1
1

answered Oct 25 '12 at 02:57

dinkelk

2,676
3
26
46

score 3 · Answer 2 · answered Oct 08 '12 at 03:57

There's an SQLAlchemy extension called SqlSoup which removes the need for most of the setup:

from sqlalchemy.ext.sqlsoup import SqlSoup
db = SqlSoup('mysql://scott:mypassword@localhost/employees')

Then to run an SQL query, see the raw SQL section of the SqlSoup docs:

rp = db.execute('select name, email from users where name like :name order by name', name='%Bhargan%')
for name, email in rp.fetchall():
    print name, email

Or if you only want one result, use the same db.execute call, then:

name, email = rp.fetchone()

You can also use SQLAlchemy's features, like it's query syntax instead of writing SQL.

Never heard of it either, I'll check it out. Much appreciated! — zachd1_618, Oct 08 '12 at 04:28

John La Rooy · Answer 3 · 2012-10-08T03:59:46.663

Sure, you could write a generator like this

import datetime
import mysql.connector

def do_mysql(query, *args):
    cnx = mysql.connector.connect(user='scott', database='employees')
    cursor = cnx.cursor()
    cursor.execute(query, args)
    for result in cursor:
        yield result
    cursor.close()
    cnx.close()

But now the username and database are hardcoded into the function. MATLAB must have these parameters stored somewhere too though.

You could pull the username and database out as extra parameters, but then you are heading back to the same level of complexity - without the advantages of being able to have control over connection pooling, etc.

def do_mysql(user, database, query, *args):
    cnx = mysql.connector.connect(user=user, database=database)
    cursor = cnx.cursor()
    cursor.execute(query, args)
    for result in cursor:
        yield result
    cursor.close()
    cnx.close()

So to get the performance we need from a program handling lots of database queries, we'll need to at least pass the connection in

def do_mysql(cnx, query, *args):
    cursor = cnx.cursor()
    cursor.execute(query, args)
    for result in cursor:
        yield result
    cursor.close()

Ah, now there's not really any guts to this function and all the parametric parts of the code have been pushed back out to the caller

+1 for laying out how to extract the complexity myself. Thanks! I'll check it off in a day or two provided no other answers arise. — zachd1_618, Oct 08 '12 at 04:31

Simple Python-MySQL Bridge?

3 Answers3