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 coolThis example works out of the box. Nothing other than having peewee (
pip install peewee:-)) is required. No complicated setup. It’s really cool.
M's Web Dev 