MySQL database design: detailed explanation of Schema operation method using Python
|
Before the arrow was shot, the bow whispered to the arrow, "Your freedom is mine." Schema is like an arrow and the bow is like Python. Choosing Python is the greatest freedom of Schema. And freedom should be an opportunity to make yourself better. What is Schema? No matter what application we make, as long as we deal with user input, there is a principle - never trust the user's input data. This means that we have to strictly validate user input. When developing a web application, input data is usually sent to the backend API in the form of JSON, and the API needs to validate the input data. Usually I add a lot of judgments and various ifs, which makes the code very ugly. Is there a more elegant way to verify user data? This is where Schema comes in handy. （Ⅰ）MySQLdb part 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: [email protected] #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 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 calling execute(), the cursor should set its description attribute
[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 Accessing column values by column name By default, the value returned from the database as a "row" by the get method 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]: 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 it 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 2. SQLAlchemy--SQL Alchemist Although SQL has international standards, unfortunately, each database vendor interprets these standards differently and implements their own private syntax based on the standards. In order to hide the differences between different SQL "dialects", people developed tools such as SQLAlchemy SQLAlchemy connection module:
[root@DataHacker Desktop]# cat sa.py
import sqlalchemy as sa
engine = 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 deletion There are 2 ways, one of which is: In [5]: t.drop(bind=engine,checkfirst=True) Another one is: In [5]: metadata.drop_all(bind=engine,checkfirst=True), where you can use the tables attribute to specify the objects to be deleted. Example 3: 5 constraints
3.1 primary key
The following two methods are both acceptable, one is column level and the other is 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
Although it will work, MySQL currently does not support check constraints. I won’t give any examples here.
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 Values There are two categories: pessimistic (values provided by the DB Server) and optimistic (values provided by SQLAlshemy), where optimistic 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='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) 3. Hide Schema Whether the security of data is exposed to a completely trustworthy object is a risk that no security-conscious DBA would take. A better approach is to hide the Schema structure as much as possible and verify the integrity of the data entered by the user. Although this increases the operation and maintenance costs to a certain extent, security is of paramount importance. Here we use the development of a command line tool to illustrate this problem Requirements: Hide the table structure, implement dynamic query, and simulate mysql \G output of the results 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 -o OUTFILE assign output file The effect we want: [root@DataHacker ~]# ./sesc.py -t actor -c last_name -qs% -f -o output.txt [root@DataHacker ~]# cat output.txt ************ 1 row ******************* actor_id: 180 first_name: JEFF last_name: SILVERSTONE last_update: 2006-02-15 04:34:33 ************ 2 row ******************* actor_id: 195 first_name: JAYNE last_name: SILVERSTONE last_update: 2006-02-15 04:34:33 ......<most of the output omitted here>...... Please see the code
#!/usr/bin/env python
import optparse
from dbapi import *
#Construct an OptionParser instance and configure the expected options parser = optparse.OptionParser(usage="%prog [options] <arg1> <arg2> [<arg3>...]",version='1.0',)
#Define command line options, using add_option to add one at a time.parser.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 command line options, args = parser.parse_args()
#Assign the above dest value to our 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.execute(statement)
results = cur.fetchall()
#Simulate \G output format 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 + 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 results 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 connection conn.close()
cur.close()
Summarize The above is all the content of this article on the detailed explanation of the Schema operation method using Python in MySQL database design. I hope it will be helpful to everyone. Welcome to refer to: Python timer example code, Python digital image generation code sharing, etc. If you have any questions, you can leave a message at any time. The editor will reply to you in time. Welcome to leave a message for discussion. You may also be interested in:
|
<<: Springboot integrates docker deployment to implement two ways to build Docker images
>>: JavaScript implementation of the Game of Life
Recommend
Solution to Django's inability to access static resources with uwsgi+nginx proxy
When deploying uwsgi+nginx proxy Django, access u...
JavaScript explains the encapsulation and use of slow-motion animation
Implementing process analysis (1) How to call rep...
Implementation of static website layout in docker container
Server placement It is recommended to use cloud s...
Detailed explanation of how to use WeChat mini program map
This article example shares the specific implemen...
Implementation of CSS dynamic height transition animation effect
This question originated from a message on Nugget...
MySQL SQL statement performance tuning simple example
MySQL SQL statement performance tuning simple exa...
JavaScript to implement image preloading and lazy loading
This article shares the specific code for impleme...
MySQL index principle and query optimization detailed explanation
Table of contents 1. Introduction 1. What is an i...
How to use JavaScript to get the most repeated characters in a string
Table of contents topic analyze Objects of use So...
Let's talk about Vue's mixin and inheritance in detail
Table of contents Preface Mixin Mixin Note (dupli...
Detailed analysis of the MySQL slow log opening method and storage format
In development projects, we can monitor SQL with ...
Detailed explanation of MySQL backup process using Xtrabackup
Table of contents 01 Background 02 Introduction 0...
A friendly alternative to find in Linux (fd command)
The fd command provides a simple and straightforw...
How to implement scheduled automatic backup of MySQL under CentOS7
The happiest thing that happens in a production e...
Detailed implementation plan of Vue front-end exporting Excel files
Table of contents 1. Technology Selection 2. Tech...