MySQL database design-detailed description of Schema operations using Python, pythonschema

MySQL database design-detailed description of Schema operations using Python, pythonschema

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.

What is Schema?

No matter what applications we do, as long as we deal with user input, there is a principle-Never trust user input data. This means that we need to strictly verify user input. During web development, the input data is generally sent to the backend API in JSON format, and the API needs to verify the input data. Generally, I add a lot of judgments, and if leads to ugly code. Is there a more elegant way to verify user data? Schema is useful.

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

1 print the column metadata

[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.) 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) 

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]: 2L In [5]: results = cur.fetchone() In [6]: print results[0] 58 In [7]: print results[1] AKROYD 

We can use the cursorclass attribute to return data as a dictionary.

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 

(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:

[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() 

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 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.

Example 4.1: 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 = 'datahacker ')) 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

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.

This issue is described by developing a command line tool.

Requirement: Hide the table structure for dynamic query and simulate mysql \ G output.

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> ......

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

Summary

The above is all the details about MySQL Database Design Using the Schema method of Python, and I hope to help you. Please refer to: Python timer instance code, Python generation digital image code sharing, etc. If you have any questions, please feel free to leave a message. The editor will reply to you in a timely manner. Please leave a message for discussion.