MySQL Schema 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
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(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)

Database connection module:

[root@DataHacker ~]# cat dbapi.py#!/usr/bin/env ipython#coding = utf-8#Author: linwaterbin@gmail.com#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 print the column metadata

[root@DataHacker ~]# 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.description:    print recordcur.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.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. Access the column value through the column name
By default, the value returned from the database as a "row" is a tuples.

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 attribute to return data 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"In [7]: cur.execute(v_sql)Out[7]: 2LIn [8]: results = cur.fetchone()In [9]: print results['actor_id']58In [10]: print results['last_name']AKROYD

(Ii) SQLAlchemy-SQL alchemy
Although SQL has international standards, it is a pity that database vendors have different interpretations of these standards and have implemented their own private syntax based on the standards. To hide the differences between different SQL dialects, tools such as SQLAlchemy were developed.
SQLAlchemy connection module:

SQLAlchemy connection module: [root @ DataHacker Desktop] # cat sa. pyimport sqlalchemy as saengine = sa. create_engine ('mysql: // root: oracle@127.0.0.1/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: delete a table

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.

Example 3: five constraints

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 KeyIn [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 uniqueIn [17]: t_uni = Table ('t _ uni', metadata, Column ('id ', integer, unique = True) In [18]: t_uni.create (bind = engine) In [1 9]: 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 is successful, but MySQL does not currently support the check constraint. Here is not an example. 3.5 not nullIn [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: 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 Example: 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) example 3.3: 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
Whether data security is exposed to completely trusted objects is a risk that no security-aware DBA will take. The better way is to hide the Schema structure as much as possible and verify the integrity of the data entered by the user. This increases the O & M cost to a certain extent, but security is no small matter.
Here, we use a command line tool to describe this problem: hiding the table structure, implementing dynamic queries, and simulating mysql \ G output results.

Version: [root @ DataHacker ~] #./Sesc. py -- version1.0 view help: [root @ DataHacker ~] #. /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 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: 180first_name: JEFFlast_name: SILVERSTONElast_update: 04:34:33 *************** 2 row ********************** actor_id: 195first_name: JAYNElast_name: SILVERSTONElast_update: 2006-02-15 04:34:33 ...... <most output is omitted here> ......

Please refer to the code

#! /Usr/bin/env pythonimport optparsefrom 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. tablecolumn = options. colterm = options. termformat = 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 ()

By DataHacker2014-2-5Good Luck!