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
Detailed explanation of how Angular handles unexpected exception errors
Written in front No matter how well the code is w...
MySQL 8.0.22 winx64 installation and configuration graphic tutorial
mysql 8.0.22 winx64 installation and configuratio...
CSS to achieve zoom in and out close button (example code)
This effect is most common on our browser page. L...
Some data processing methods that may be commonly used in JS
Table of contents DOM processing Arrays method Su...
Detailed steps to start the Django project with nginx+uwsgi
When we develop a web project with Django, the te...
Detailed description of shallow copy and deep copy in js
Table of contents 1. js memory 2. Assignment 3. S...
Detailed explanation of how to use amoeba to implement read-write separation of MySQL database
There are many read-write separation architecture...
MySQL 8.0.12 Installation and Configuration Tutorial
This article records the detailed tutorial for in...
Example code of CSS layout at both ends (using parent's negative margin)
Recently, during the development process, I encou...
Detailed explanation of the difference between JavaScript spread operator and rest operator
Table of contents What is the rest operator? How ...
Jenkins packaging microservices to build Docker images and run them
Table of contents Environment Preparation start 1...
404 error occurs when accessing the homepage of tomcat started in Docker mode
Scenario: When starting tomcat in docker (version...
Let you understand how HTML and resources are loaded
All content in this blog is licensed under Creati...
CenOS6.7 mysql 8.0.22 installation and configuration method graphic tutorial
CenOS6.7 installs MySQL8.0.22 (recommended collec...
Using js to realize dynamic background
This article example shares the specific code of ...