pythonbitscn.com
弓在箭要射出之前，低声对箭说道，“你的自由是我的”。schema如箭，弓似python，选择python，是schema最大的自由。而自由应是一个能使自己变得更好的机会。
㈠ mysqldb部分
mysql> use sakila; mysql> desc actor; +-------------+----------------------+------+-----+-------------------+-----------------------------+ | field | type | null | key | default | extra | +-------------+----------------------+------+-----+-------------------+-----------------------------+ | actor_id | smallint(5) unsigned | no | pri | null | auto_increment | | first_name | varchar(45) | no | | null | | | last_name | varchar(45) | no | mul | null | | | last_update | timestamp | no | | current_timestamp | on update current_timestamp | +-------------+----------------------+------+-----+-------------------+-----------------------------+ 4 rows in set (0.00 sec) [root@datahacker ~]# cat dbapi.py #!/usr/bin/env ipython #coding = utf-8 #author: linwaterbin@gmail.com #time: 2014-1-29 import mysqldb as dbapi user = 'root' passwd = 'oracle' host = '127.0.0.1' db = 'sakila' conn = dbapi.connect(user=user,passwd=passwd,host=host,db=db) [root@datahacker ~]# cat querycolumnmetadata.py #!/usr/bin/env ipython from dbapi import * cur = conn.cursor() statement = """select * from actor limit 1""" cur.execute(statement) print "output column metadata....." print for record in cur.description: print record cur.close() conn.close()
1.）调用execute()之后，cursor应当设置其description属性
2.）是个tuple，共7列：列名、类型、显示大小、内部大小、精度、范围以及一个是否接受null值的标记
[root@datahacker ~] # chmod +x querycolumnmetadata.py[root@datahacker ~] # ./querycolumnmetadata.pyoutput column metadata..... ('actor_id', 2, 1, 5, 5, 0, 0)('first_name', 253, 8, 45, 45, 0, 0) ('last_name', 253, 7, 45, 45, 0, 0)('last_update', 7, 19, 19, 19, 0, 0) in [1]: from dbapi import * in [2]: cur = conn.cursor() in [3]: v_sql = "select actor_id,last_name from actor limit 2" in [4]: cur.execute(v_sql) out[4]: 2l in [5]: results = cur.fetchone() in [6]: print results[0] 58 in [7]: print results[1] akroyd in [2]: import mysqldb.cursors in [3]: import mysqldb in [4]: conn = mysqldb.connect(user='root',passwd='oracle',host='127.0.0.1',db='sakila', cursorclass=mysqldb.cursors.dictcursor) in [5]: cur = conn.cursor() in [6]: v_sql = "select actor_id,last_name from actor limit 2" in [7]: cur.execute(v_sql) out[7]: 2l in [8]: results = cur.fetchone() in [9]: print results['actor_id'] 58 in [10]: print results['last_name'] akroyd
㈡ sqlalchemy--sql炼金术师
sqlalchemy连接模块：
[root@datahacker desktop] # cat sa.py import sqlalchemy as sa engine = sa.create_engine('mysql://root:oracle@127.0.0.1/testdb',pool_recycle=3600) metadata = sa.metadata() in [3]: t = table('t',metadata, ...: column('id',integer), ...: column('name',varchar(20)), ...: mysql_engine='innodb', ...: mysql_charset='utf8' ...: ) in [4]: t.create(bind=engine)
有2种方式，其一： in [5]: t.drop(bind=engine,checkfirst=true) 另一种是： in [5]: metadata.drop_all(bind=engine,checkfirst=true)，其中可以借助tables属性指定要删除的对象
3 .1 primary key 下面2种方式都可以，一个是列级，一个是表级
in [7]: t_pk_col = table('t_pk_col',metadata,column('id',integer,primary_key=true),column('name',varchar(20))) in [8]: t_pk_col.create(bind=engine) in [9]: t_pk_tb = table('t_pk_01',metadata,column('id',integer), column('name',varchar(20)),primarykeyconstraint('id','name',name='prikey')) in [10]: t_pk_tb.create(bind=engine) 3.2 foreign key in [13]: t_fk = table('t_fk',metadata,column('id',integer,foreignkey('t_pk.id'))) in [14]: t_fk.create(bind=engine) in [15]: t_fk_tb = table('t_fk_tb',metadata,column('col1',integer),column('col2',varchar(10)), foreignkeyconstraint(['col1','col2'],['t_pk.id','t_pk.name'])) in [16]: t_fk_tb.create(bind=engine) 3.3 unique in [17]: t_uni = table('t_uni',metadata,column('id',integer,unique=true)) in [18]: t_uni.create(bind=engine) in [19]: t_uni_tb = table('t_uni_tb',metadata,column('col1',integer), column('col2',varchar(10)),uniqueconstraint('col1','col2')) in [20]: t_uni_tb.create(bind=engine) 3.4 check 虽然能成功，但mysql目前尚未支持check约束。这里就不举例了。 3.5 not null in [21]: t_null = table('t_null',metadata,column('id',integer,nullable=false)) in [22]: t_null.create(bind=engine)
4 默认值
分2类：悲观(值由db server提供)和乐观(值由sqlalshemy提供)，其中乐观又可分：insert和update
4.1 例子：
insertin [23]: t_def_inser = table('t_def_inser',metadata,column('id',integer), column('name',varchar(10),server_default='cc')) in [24]: t_def_inser.create(bind=engine)
3.2 例子：
updatein [25]: t_def_upda = table('t_def_upda',metadata,column('id',integer), column('name',varchar(10),server_onupdate='datahacker')) in [26]: t_def_upda.create(bind=engine)
3.3 例子：
passivein [27]: t_def_pass = table('t_def_pass',metadata,column('id',integer), column('name',varchar(10),defaultclause('cc'))) in [28]: t_def_pass.create(bind=engine)
㈢ 隐藏schema
版本： [root@datahacker ~]# ./sesc.py --version 1.0 查看帮助： [root@datahacker ~]# ./sesc.py -h usage: sesc.py [options]
#!/usr/bin/env python import optparse from dbapi import * #构造optionparser实例，配置期望的选项 parser = optparse.optionparser(usage=%prog [options]
以上就是mysqlschema设计（三）利用python操作schema_mysql的内容。