Detailed Explanation of MySQL Database Design Using Python to Operate Schema
- 2021-09-16 08:22:00
- OfStack
The bow whispered to the arrow before it was about to shoot, "Your freedom is mine". Schema is like an arrow and a 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 do, as long as we deal with user input, there is a principle-never trust user input data. It means that we should strictly verify the user's input. When web is developed, all the input data are sent to the back-end API in the form of JSON, and API should verify the input data. 1 I am adding a lot of judgments, all kinds of if, resulting in ugly code, can there be a more elegant way to verify user data? Schema will come in handy.
(i) 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: 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 metadata for columns
[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 (), cursor should set its description property
2.) is an tuple with 7 columns: column name, type, display size, internal size, precision, range, and a flag to accept 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 column values by column name
By default, the value returned by the get method as a "row" from the database 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 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 Alchemist
Although SQL has international standards, unfortunately, each database vendor has different interpretations of these standards, and all of them have implemented their own private syntax based on the standards. In order to hide the differences between different SQL "dialects", tools such as SQLAlchemy have been 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: Table Delete
Have 2 In a way, its 1 :
In [5]: t.drop(bind=engine,checkfirst=True)
Another 1 The species are:
In [5]: metadata.drop_all(bind=engine,checkfirst=True) , which can be used by means of tables Property specifies the object to delete
example 3: 5 Constraints
3 .1 primary key
Below 2 You can do it in any way, 1 One is column level, 1 One is the 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 can succeed, MySQL Currently not supported check Constraints. I won't give an example 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
There are two categories: pessimism (value provided by DB Server) and optimism (value provided by SQLAlshemy), among which optimism can be divided into insert and update
[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)
0
(iii) Hide Schema
Whether data security is exposed to fully trusted objects is a risk that any security-conscious DBA will not take. A better way is to hide the Schema structure as much as possible and verify the integrity of the data input by users. Although this increases the operation and maintenance cost to a certain extent, security is no small matter.
Here, a command line tool is developed to illustrate this problem
Requirements: Hide the table structure, realize dynamic query, and simulate the output of mysql\ G
[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
Look at the code
[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)
2
Summarize
The above is this article about MySQL database design using Python operation Schema method detailed explanation of the whole content, I hope to help you. Welcome to see: Python timer example code, Python generated digital picture code sharing, any questions can leave a message at any time, this site will reply to everyone in time, welcome to leave a message exchange discussion.