


Detailed explanation of MySQL database using Python to operate Schema method
What is Schema?
No matter what application we make, as long as we deal with user input, there is one principle - never trust the user's input data. This means that we need to strictly verify user input. In web development, input data is generally sent to the back-end API in JSON form, and the API needs to verify 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? Schema comes in handy. This article mainly introduces the detailed explanation of the method of using Python to operate Schema in MySQL database design. It is quite good. I will share it with you here for the reference of friends who need it.
㈠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 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
2.) It is a tuple with a total of 7 columns: column name, type, display size, internal size , precision, range, and a flag indicating whether 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 Accessing column values by column name
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
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
#㈡ SQLAlchemy--SQL Alchemist
Although SQL has International standards, but unfortunately, each database manufacturer has different interpretations of these standards, and they all implement their own private syntax based on the standards. In order to hide the differences between different SQL "dialects", people have developed tools such as SQLAlchemy
[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 deletion
有2种方式,其一: In [5]: t.drop(bind=engine,checkfirst=True) 另一种是: In [5]: metadata.drop_all(bind=engine,checkfirst=True),其中可以借助tables属性指定要删除的对象
example 3: 5 kinds of constraints
3 .1 primary key 下面2种方式都可以,一个是列级,一个是表级 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 虽然能成功,但MySQL目前尚未支持check约束。这里就不举例了。 3.5 not null In [21]: t_null = Table('t_null',metadata,Column('id',Integer,nullable=False)) In [22]: t_null.create(bind=engine)
Divided into 2 categories: pessimism (value provided by DB Server) and optimism (value provided by SQLAlshemy). Optimism can be divided into: insert and update
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 例子: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 例子: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)
㈢ Hidden Schema
#Whether the security of the data is exposed to a completely trustworthy object, this is something that no security-conscious DBA will risk. risk. A better way 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 cost to a certain extent, it is not trivial to be safe.
Here we develop a command line tool to illustrate this problem
Requirements: hide the table structure, implement dynamic query, and simulate the results to mysql \G output
版本: [root@DataHacker ~]# ./sesc.py --version 1.0 查看帮助: [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 我们要的效果: [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: 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 ......<此处省略大部分输出>......
Please see the code
#!/usr/bin/env python import optparse from dbapi import * #构造OptionParser实例,配置期望的选项 parser = optparse.OptionParser(usage="%prog [options] <arg1> <arg2> [<arg3>...]",version='1.0',) #定义命令行选项,用add_option一次增加一个 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") #解析命令行 options,args = parser.parse_args() #把上述dest值赋给我们自定义的变量 table = options.table column = options.col term = options.term format = options.format #实现动态读查询 statement = "select * from %s where %s like '%s'"%(table,column,term) cur = conn.cursor() cur.execute(statement) results = cur.fetchall() #模拟 \G 输出形式 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) #把输出结果定向到指定文件 if options.outfile: outfile = options.outfile with open(outfile,'w') as out: out.write(output) else: print output #关闭游标与连接 conn.close() cur.close()
Related recommendations:
Detailed explanation of the difference between table and schema in the databaseDetailed introduction to information_schema in MySQLMySQL distributed cluster MyCAT (2) schema Detailed code explanationThe above is the detailed content of Detailed explanation of MySQL database using Python to operate Schema method. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Python excels in automation, scripting, and task management. 1) Automation: File backup is realized through standard libraries such as os and shutil. 2) Script writing: Use the psutil library to monitor system resources. 3) Task management: Use the schedule library to schedule tasks. Python's ease of use and rich library support makes it the preferred tool in these areas.

VS Code extensions pose malicious risks, such as hiding malicious code, exploiting vulnerabilities, and masturbating as legitimate extensions. Methods to identify malicious extensions include: checking publishers, reading comments, checking code, and installing with caution. Security measures also include: security awareness, good habits, regular updates and antivirus software.

VS Code can run on Windows 8, but the experience may not be great. First make sure the system has been updated to the latest patch, then download the VS Code installation package that matches the system architecture and install it as prompted. After installation, be aware that some extensions may be incompatible with Windows 8 and need to look for alternative extensions or use newer Windows systems in a virtual machine. Install the necessary extensions to check whether they work properly. Although VS Code is feasible on Windows 8, it is recommended to upgrade to a newer Windows system for a better development experience and security.

VS Code can be used to write Python and provides many features that make it an ideal tool for developing Python applications. It allows users to: install Python extensions to get functions such as code completion, syntax highlighting, and debugging. Use the debugger to track code step by step, find and fix errors. Integrate Git for version control. Use code formatting tools to maintain code consistency. Use the Linting tool to spot potential problems ahead of time.

Python is more suitable for beginners, with a smooth learning curve and concise syntax; JavaScript is suitable for front-end development, with a steep learning curve and flexible syntax. 1. Python syntax is intuitive and suitable for data science and back-end development. 2. JavaScript is flexible and widely used in front-end and server-side programming.

VS Code performs well on macOS and can improve development efficiency. The installation and configuration steps include: installing VS Code and configuring. Install language-specific extensions (such as ESLint for JavaScript). Install the extensions carefully to avoid excessive startup slowing down. Learn basic features such as Git integration, terminal and debugger. Set the appropriate theme and code fonts. Note potential issues: extended compatibility, file permissions, etc.

Yes, VS Code can run Python code. To run Python efficiently in VS Code, complete the following steps: Install the Python interpreter and configure environment variables. Install the Python extension in VS Code. Run Python code in VS Code's terminal via the command line. Use VS Code's debugging capabilities and code formatting to improve development efficiency. Adopt good programming habits and use performance analysis tools to optimize code performance.

Executing code in VS Code only takes six steps: 1. Open the project; 2. Create and write the code file; 3. Open the terminal; 4. Navigate to the project directory; 5. Execute the code with the appropriate commands; 6. View the output.
