sqlalchemy学习笔记

第一:安装
sudo easy_install SQLAlchemy

第二:基本的使用:

1 先写个脚本 尝试连接mysql ,创建一张表:

# -*- coding: UTF-8 -*-
from sqlalchemy import *
import sqlalchemy.util as util
import string, sys
from sqlalchemy.databases import mysql
mysql_engine = create_engine('mysql://root:password@localhost:3306/db_name?charset=utf8',encoding = "utf-8",echo =True)   
#mysql_engine.connect()    
metadata = MetaData()
#创建users表
users_table = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('username', String(20), nullable = False),
    Column('fullname', String(20), nullable = False),
    Column('password', String(20), nullable = False),
    mysql_engine='InnoDB'
)
#mysql_engine='InnoDB' 或者 mysql_engine='MyISAM' 表类型
metadata.create_all(mysql_engine)

 

 

2 创建映射类:

from sqlalchemy import Column,Integer,String
class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer,primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(50))
    #email  = Column(String(250))
    def __init__(self,name,fullname,password):
    # def __init__(self,name,fullname,password,email):
        self.name = name 
        self.fullname = fullname
        self.password = password
        #self.email = email
    def __repr__(self):
        return "<User('%s','%s','%s')>" % (self.name,self.fullname,self.password)
    
from sqlalchemy import *
import sqlalchemy.util as util
import string, sys
from sqlalchemy.databases import mysql
mysql_engine = create_engine('mysql://root:password@localhost:3306/notewo?charset=utf8',encoding = "utf-8",echo =True)   
Base.metadata.create_all(mysql_engine)

 

 

到了这里会创建一个映射类,映射到一张表.

3 然后,如果我们想要增加一个column,要怎么办呢?sqlalchemy 本身是没有提供这个功能的.不过我们可以通过这个工具来使用这个功能 .
官方教程
初步学习,使用方法如下:
1 安装  :

ubuntu@yee:~/notewo/test$ sudo easy_install sqlalchemy-migrate

2 创建一个repository :

ubuntu@yee:~/notewo$ migrate create notewo "NWO project"

notewo是项目的目录名字

3 创建 版本控制:

ubuntu@yee:~/notewo$ python notewo/manage.py  version_control mysql://root:password@localhost/notewo notewo

 

4 查看当前版本:

ubuntu@yee:~/notewo$ python notewo/manage.py  db_version mysql://root:password@localhost/notewo notewo
0

5 查看最后可用的版本:

ubuntu@yee:~/notewo$ python notewo/manage.py  version notewo
0

6 创建管理脚本 :如果每次操作都需要输入URL会很烦人

migrate manage manage.py --repository=notewo --url=mysql://root:password@localhost/notewo
ubuntu@yee:~/notewo$ python manage.py  db_version
0

7 demo ,现在我们添加一个email field :

ubuntu@yee:~/notewo$ python manage.py script "Add email column"
ubuntu@yee:~/notewo$ vim notewo/versions/001_Add_email_column.py
def upgrade(migrate_engine):
   meta = MetaData(bind=migrate_engine)
   users = Table('users',meta,autoload=True)
   emailc = Column('email',String(250))
   emailc.create(users)

def downgrade(migrate_engine):
   meta = MetaData(bind=migrate_engine)
   users = Table('users',meta,autoload=True)
   users.c.email.drop()

ubuntu@yee:~/notewo$ python manage.py  upgrade
0 -> 1... 
done

 

比起django 的django_south 是相对麻烦一些的,不够自动化.不过也够用,麻烦就麻烦吧.