MySQLSchema design (3) using Python to operate Schema

The bow whispered to the arrow, "Your freedom is mine ". Schema is like an arrow and bows like Python. Choosing Python is the biggest freedom of Schema. Freedom should be an opportunity to make yourself better.

(I) MySQLdb Section
 
Mysql> use sakila; mysql> desc actor; + ------------- + hour + ------ + ----- + ----------------- + hour + | Field | Type | Null | Key | Default | Extra | + ------------- + hour + ------ + ----- + ------------------- + hour + | 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 | + ------------- + hour + ------ + ----- + ------------------- + hour + 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 = '2017. 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.exe cute (statement) print" output column metadata ..... "print for record in cur. description: print record cur. close () conn. close ()

1.) After execute () is called, cursor should set its description attribute
 

2) is a tuple with a total of 7 columns: column name, type, display size, internal size, accuracy, range, and a flag that accepts null values
[Root @ DataHacker ~] # Chmod + x QueryColumnMetaData. py
[Root @ DataHacker ~] #./QueryColumnMetaData. py
Output 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.exe cute (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 = '2017. 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.exe cute (v_ SQL) Out [7]: 2L In [8]: results = cur. fetchone () In [9]: print results ['actor _ id'] 58 In [10]: print results ['last _ name'] AKROYD

(Ii) SQLAlchemy-SQL alchemy

SQLAlchemy connection module: [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)

There are two ways: In [5]: t. drop (bind = engine, checkfirst = True) Another type is: In [5]: metadata. drop_all (bind = engine, checkfirst = True). You can use the tables attribute to specify the object to be deleted.

3. 1 primary key can be used In either of the following two methods: column-level and Table-level 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, however, MySQL does not currently support the check constraint. Here is not an example. 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 Default Value

There are two types: pessimistic (the value is provided by the DB Server) and optimistic (the value is provided by SQLAlshemy). Optimism can be divided into insert and update.

4.1 example: insert
In [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 Example: update
In [25]: t_def_upda = Table ('t_ def_upda ', metadata, Column ('id', Integer), Column ('name', VARCHAR (10 ), server_onupdate = 'dateacker '))

In [26]: t_def_upda.create (bind = engine)
3.3 example: Passive
In [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)

(Iii) Hide Schema

Version: [root @ DataHacker ~] #./Sesc. py -- version 1.0 view help: [root @ DataHacker ~] #. /Sesc. py-h Usage: sesc. py [options] <arg1> <arg2> [<arg3>...] options: -- version show program's version number and exit-h, -- help show this help message and exit-q TERM assign where predicate-c COL, -- column = COL assign query column-t TABLE assign query table-f, -- format-f must match up-o OUTFILE assign output file: [root @ DataHacker ~] #./Sesc. py-t actor-c last_name-q s %-f-o output.txt [root @ DataHacker ~] # Cat output.txt ************** 1 row ********************* actor_id: 180 first_name: JEFF last_name: SILVERSTONE last_update: 04:34:33 *************** 2 row ********************** actor_id: 195 first_name: JAYNE last_name: SILVERSTONE last_update: 04:34:33 ...... <most output is omitted here> ......

#! /Usr/bin/env python import optparse from dbapi import * # construct an OptionParser instance and configure the expected option parser = optparse. optionParser (usage = "% prog [options] <arg1> <arg2> [<arg3>...] ", version = '1. 0',) # define the command line option. Use add_option to add a parser at a time. add_option ("-q", action = "store", type = "string", dest = "term", help = "assign where predicate") parser. add_option ("-c", "-- column", action = "store", type = "string", dest = "col", help = "assign query column") parser. add_option ("-t", action = "store", type = "string", dest = "table", help = "assign query table") parser. add_option ("-f", "-- format", action = "store_true", dest = "format", help = "-f must match up-o") parser. add_option ("-o", action = "store", type = "string", dest = "outfile", help = "assign output file") # parse the command line options, args = parser. parse_args () # assign the preceding dest value to the custom variable table = options. table column = options. col term = options. term format = options. format # implement dynamic read query statement = "select * from % s where % s like '% S'" % (table, column, term) cur = conn. cursor () cur.exe cute (statement) results = cur. fetchall () # simulate \ G output format if format is True: columns_query = "describe % s" % (table) cur.exe cute (columns_query) heards = cur. fetchall () column_list = [] for record in heards: column_list.append (record [0]) output = "" count = 1 for record in results: output = output + "************** % s row ************ \ n" % (count) for field_no in xrange (0, len (column_list): output = output + column_list [field_no] + ":" + str (record [field_no]) + "\ n" output = output + "\ n" count = count + 1 else: output = [] for record in xrange (0, len (results): output. append (results [record]) output = ''. join (output) # direct the output to the specified file if options. outfile: outfile = options. outfile with open (outfile, 'w') as out: out. write (output) else: print output # Close the cursor and connect to conn. close () cur. close ()