SQLAlchemy Python module with MySQL

SQLAlchemy is such a great Python module to work with DBMs. This blog is about how to manipulate MySQL database with SQLAlchemy.

1. Install MySQL driver for Python:

$ sudo apt-get build-dep python-mysqldb

$ sudo pip install MySQL-python

2. Install SQLAlchemy:

$ sudo pip install SQLAlchemy

3. Example:

- We have a database name mydb which contains 1 table, name mytable:

We gonna implement some method to select, insert, update, or delete the database above:

a. Connect to the mydb database:

from sqlalchemy import create_engine
engine = create_engine('mysql://mydb_user:mydb_pwd@localhost:3306/mydb', echo=False)

b. Call the session which bind the db engine to manipulate the database:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

c. Define the models class mapper:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()
class MyTable(Base):
        __tablename__ = 'mytable'

        id = Column(Integer, primary_key=True)
        name = Column(String(100))
        value = Column(String(100))

        def __init__(self, name, value):
                self.name = name
                self.value = value

        def __repr__(self):
                return "<MyTable(%s, %s)>" % (self.name, self.value)

d. Have fun:

- Add a new record to mytable:

new_record = MyTable('Genius', 'me')
session.add(new_record)
session.commit()

or add multiple records at one:

list_or_records = [MyTable('Genius', 'me'), MyTable('Super', 'me')]
session.add_all(list_of_records)
session.commit()

- Query mytable:

records = session.query(MyTable).filter_by(name='Genius')

or

all_records = session.query(MyTable).all()

- Delete records:

records_to_delete = session.query(MyTable).filter_by(name='Super')
for record in records_to_delete:
        session.delete(record)
session.commit()


Please read the official tutorial of SQLAlchemy for deeper understanding: http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html