MySQL schema Design (iii) manipulating schemas with Python

Source: http://blog.itpub.net/26515977/viewspace-1208256/SQLAlchemy Part of this article can refer to http://www.liaoxuefeng.com/wiki/ 0014316089557264a6b348958f449949df42a6d3a2e542c000/0014320114981139589ac5f02944601ae22834e9c521415000 Bow before the arrow is going to shoot, Whispered to the arrow, "Your Freedom is mine." Schema like arrow, bow like python, choose Python, is the schema of the largest freedom. And freedom should be a chance to make yourself better. ㈠MYSQLDB Partial table structure:

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 |          NO   |     | NULL              |                             | | last_name   | varchar |          NO   | MUL | NULL              |                             | | last_update | timestamp            | NO   |     | Current_timestamp | On update current_timestamp |+-------------+----------------------+------+-----+-------------------+------------- ----------------+4 rows in Set (0.00 sec)

Database Connection module:

[email protected] ~]# cat dbapi.py#!/usr/bin/env ipython#coding = utf-8#author: [email protected] #Time: 2014-1-29import MySQLdb as Dbapiuser = \ ' root\ ' PASSWD = \ ' oracle\ ' HOST = \ ' 127.0.0.1\ ' DB = \ ' sakila\ ' conn = Dbapi.connect (user=user,passwd =PASSWD,HOST=HOST,DB=DB)

1 Printing the metadata for a column

[email protected] ~]# cat querycolumnmetadata.py#!/usr/bin/env ipythonfrom dbapi Import *cur = conn.cursor () statement = " "SELECT * FROM actor limit 1" "Cur.execute (statement) print" Output column metadata ... "Printfor record in Cur.descripti On:    print Recordcur.close () conn.close ()

1.) after calling execute (), the cursor should set its Description property

2.) is a tuple of 7 columns: column name, type, display size, internal size, precision, range, and a tag that accepts null values

[Email protected] ~]# chmod +x querycolumnmetadata.py[[email protected] ~]#./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)

2 accessing column values by column name by default, gets the value returned by the method from the database as "row" is a tuple

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]: 2LIn [5]: results = Cur.fetchone () in [6]: Print results[0]58in [7]: Print Results[1]akroyd

We can use the Cursorclass property to return as a dictionary

In [2]: Import Mysqldb.cursorsin [3]: Import Mysqldbin [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 "inch [7]: Cur.execute (v_sql) out[7]: 2LIn [8]: results = Cur.fetchone () in [9]: Print re Sults[\ ' actor_id\ ']58in [ten]: Print results[\ ' last_name\ ']akroyd

㈡sqlalchemy--sql Alchemist Although there are international standards for SQL, unfortunately, each database vendor interpretation of these standards are different, and the standard based on the implementation of their own private syntax. In order to hide the differences between different SQL "dialects", people have developed tools such as SQLAlchemy sqlalchemy connection modules:

SQLAlchemy connection module: [[email protected] desktop]# cat Sa.pyimport sqlalchemy as Saengine = Sa.create_engine (\ ' mysql://root:[ Email protected]/testdb\ ', pool_recycle=3600) metadata = sa. MetaData ()

Example 1: Table definition

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

Example 2: Table Delete

There are 2 ways, one: in [5]: T.drop (Bind=engine,checkfirst=true) Another is: in [5]: Metadata.drop_all (bind=engine,checkfirst=true), Where you can specify which objects to delete with the Tables property

Example 3:5 types of constraints

3.1 PRIMARY Key The following 2 ways can be, one is the column level, one is the table level in [7]: T_pk_col = table (\ ' T_pk_col\ ', metadata,column (\ ' id\ ', Integer,primary_key =true), Column (\ ' name\ ', VARCHAR ())) in [8]: T_pk_col.create (Bind=engine) in [9]: T_PK_TB = Table (\ ' t_pk_01\ ', metadata , column (\ ' id\ ', Integer), column (\ ' name\ ', VARCHAR ()), primarykeyconstraint (\ ' id\ ', \ ' name\ ', name=\ ' prikey\ ')) in [ Ten]: T_pk_tb.create (bind=engine) 3.2 Foreign Keyin []: T_FK = Table (\ ' T_fk\ ', metadata,column (\ ' id\ ', Integer, ForeignKey (\ ' t_pk.id\ '))) in [+]: t_fk.create (Bind=engine) in []: T_FK_TB = Table (\ ' t_fk_tb\ ', metadata,column (\ ') Col1\ ', Integer), Column (\ ' col2\ ', VARCHAR ()), ForeignKeyConstraint ([\ ' col1\ ', \ ' col2\ '],[\ ' t_pk.id\ ', \ ' T_pk.name \ '])) in [+]: t_fk_tb.create (bind=engine) 3.3 Uniquein [n]: T_uni = Table (\ ' t_uni\ ', metadata,column (\ ' id\ ', Integer, Unique=true) in [+]: t_uni.create (Bind=engine) in [+]: T_UNI_TB = Table (\ ' t_uni_tb\ ', metadata,column (\ ' col1\ ', Integer), Column (\ ' col2\ ', VARCHAR ()), UniqueConstraint (\ ' col1\ ', \ ' col2\ ')) in []: T_uni_tb.create(bind=engine) 3.4 Check is successful, but MySQL does not currently support check constraints. This is not an example. 3.5 not Nullin [+]: t_null = Table (\ ' t_null\ ', metadata,column (\ ' id\ ', Integer,nullable=false)) in []: T_null.create ( Bind=engine)

4 Default Value

Category 2: Pessimistic (value is provided by DB Server) and optimistic (value provided by Sqlalshemy), where optimism can be divided: insert and update

4.1 Example: Insertin []: T_def_inser = Table (\ ' T_def_inser\ ', metadata,column (\ ' id\ ', Integer), Column (\ ' name\ ', VARCHAR ( Server_default=\ ' cc\ ')) in []: T_def_inser.create (bind=engine) 3.2 Example: Updatein [+]: T_DEF_UPDA = Table (\ ' T_def_ Upda\ ', metadata,column (\ ' id\ ', Integer), Column (\ ' name\ ', VARCHAR (Ten), server_onupdate=\ ' Datahacker\ ')) in []: T_ Def_upda.create (Bind=engine) 3.3 Example: Passive in [+]: T_def_pass = Table (\ ' t_def_pass\ ', metadata,column (\ ' id\ ', Integer ), Column (\ ' name\ ', VARCHAR (Ten), Defaultclause (\ ' cc\ '))) in []: T_def_pass.create (bind=engine)

㈢ the security of the hidden schema data is exposed to fully trusted objects, which is a risk that any DBA with security awareness will not take. The better way is to hide the schema structure as much as possible and verify the data integrity of the user input, which, to a certain extent, increases the operation and maintenance costs, but security is no small matter. This is done by developing a command-line tool to illustrate the problem requirements: hiding the table structure, implementing dynamic queries, and simulating the results of MySQL \g output

Version: [[email protected] ~]#./sesc.py--version1.0 View Help: [[email protected] ~]#./sesc.py-husage: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  -o OUTFILE            Assign output file we want to effect: [[email protected] ~]#./SESC.P Y-t actor-c last_name-q s%-f-o output.txt[[email protected] ~]# cat output.txt************ 1 row *******************a Ctor_id:180first_name:jefflast_name:silverstonelast_update:2006-02-15 04:34:33************ 2 Row ***************** **actor_id:195first_name:jaynelast_name:silverstonelast_update:2006-02-15 04:34:33......< omitted most of the output here;
   

Please look at the code

#!/usr/bin/env pythonimport optparsefrom Dbapi Import * #构造OptionParser实例, configure the desired option parser = Optparse. Optionparser (usage= "%prog [options] <arg1> <arg2> [<arg3> ...]", version=\ ' 1.0\ ',) #定义命令行选项, with add_ Option to add a parser.add_option ("-Q", action= "store", type= "string", dest= "term", help= "assign where predicate" at a time) 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") #解析命令行options, args = Parser.parse_args () #把上述dest值赋给我们自定义的变量table = Options.tablecolumn = Options.colterm = Options.termformat = options.format# Implements dynamic Read query statement = "SELECT * from%s where%s Like \ '%s\ '% (table,column,term) cur = conn.cursor () cur.execute (statement) results = Cur.fetchall () #模拟 \g output form if formAt is True:columns_query = "Describe%s"% (table) Cur.execute (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\n"% (count) for Field_no in xrange (0, Len (column_list)): output = output + C   olumn_list[field_no]+ ":" + str (record[field_no]) + "\ n" output = output + "\ n" Count = count + 1else:output = [] For record in xrange (0,len (results)): Output.append (Results[record]) output = \ ' \ '. Join (output) #把输出结果定向到指定文件if Optio Ns.outfile:outfile = Options.outfile with open (outfile,\ ' w\ ') as Out:out.write (output) Else:print output# close the cursor and connect the CO Nn.close () Cur.close ()

by Datahacker2014-2-5good luck!

MySQL schema Design (iii) manipulating schemas with Python