Python and MySQL

How to work with MySQL in Python? Read below from the discussion here.  

Connecting to MYSQL with Python in 3 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.

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

For Linux, this is a casual package (python-mysqldb).

For Mac, you can install MySQLdb using Macport.

2 – Usage

After installing, reboot. This is not mandatory, but will prevent me from answering 3 or 4 others questions in this post if 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

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

I strongly advice 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.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
    print book.title

Peewee is cool

This example works out of the box. Nothing other than having peewee (pip install peewee :-)) is required. No complicated setup. It’s really cool.

Django

Tips and Tricks

  • QuerySet: “or” filter – Q objects. (here)
  • Get specific columns: QuerySet.values() or QuerySet.values_list()
  • Get first and last elements of QuerySet (here)
  • String to dict (here)

    You can deserialize it using the built-in json module if you’re on Python 2.6+, otherwise you can use the excellent third-party simplejson module.

    import json    # or `import simplejson as json` if on Python < 2.6
    
    json_string = u'{ "id":"123456789", ... }'
    obj = json.loads(json_string)    # obj now contains a dict of the data
  • Filter by multiple values (here)
  • Add custom model methods
  • Using <select multiple>: request.POST.getlist(‘services’) and request.POST.get(‘service’) (here)
  • Serialize Python sets (here)
  • URL decoding/encoding

Official documentation

Other materials

Web hosting

Python

Must-read

Packages

Packaging

Dev environment

  • Package management. Python lacks package management tool comparable with Perl CPAN or Ruby Gems, though pip and easy_install make it easier (more discussion).
  • Python IDEs. Here is a discussion at stack overflow.
  • Virtual environment – virtualenv and virtualenvwrapper

How-tos

  • Calculate entropy (here)
  • Flat a list of lists (here). [item for sublist in l for item in sublist]
  • Capture the output when running system commands. (here)

    For convenience, Python 2.7 provides the

    subprocess.check_output(*popenargs, **kwargs)  

    function, which takes the same arguments as Popen, but returns a string containing the program’s output. You’ll still want to pass stderr=subprocess.STDOUT because that will ensure that error messages are included in the returned output. See here for documentation.

    Also, although Vartec’s method will work, the better way to go if you’re using an older Python — at least in simple cases that don’t require real-time output capturing — would be to use communicate. As in:

    output = Popen(["mycmd", "myarg"], stdout=PIPE).communicate()[0]

    Or

    >>> import subprocess
    >>> p = subprocess.Popen(['ls', '-a'], stdout=subprocess.PIPE, 
                                           stderr=subprocess.PIPE)
    >>> out, err = p.communicate()
    >>> print out
    .
    ..
    foo
  • Fill out a string with spaces: str.ljust(width[, fillchar])
  • How to find the location of Python modules: foo.__file__ (more)
  • Union keys from multiple dicts (here)
  • allkey = reduce(lambda x, y: x.union(y.keys()), alldict, set())

    An alternative without any lambdas would be:

    allkey = reduce(set.union, map(set, map(dict.keys, alldict)))
  • Sorting, sort a dictionary by value
  • Declare function at the end of file
  • Print to STDERR: sys.stderr.write()
  • PyDev 3.0 does not work under OSX. Follow the user3056710‘s solution at here.
  • Install package without root access? See here.
  • Know if an object has an attribute in Python?
    if hasattr(a, 'property'):
        a.property

    Or, you really just want the value of the attribute with a default if it isn’t there, the best option is

    getattr(a, 'property', 'default value')

    More.

  • Rename a dictionary key. More about pop.
    mydict[new_key] = mydict.pop(old_key)
  • List comprehension examples (here).
    [(x, y, x * y) for x in (0,1,2,3) for y in (0,1,2,3) if x < y]
    total = sum(x+y for x in (0,1,2,3) for y in (0,1,2,3) if x < y)
  • csv DictReader and DictWriter example (here)