Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief description of the detailed steps for MySQL database to operate Schema through Python. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the detailed steps of MySQL database operating Schema through Python can bring you some practical help.

㈠ 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 metadata of the column

[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 a tuple with seven columns: column name, type, display size, internal size, precision, range, and a flag for whether or not 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 through column names

By default, the value returned by the get method from the database as a row 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 property 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

㈡ SQLAlchemy--SQL Alchemist

Although SQL has international standards, it is a pity that different database vendors interpret these standards differently and implement their own private syntax on the basis of the standards. To hide the differences between different SQL "dialects", people have developed tools such as SQLAlchemy

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.: 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 two ways, one: In [5]: t.drop (bind=engine,checkfirst=True) and the other is: In [5]: metadata.drop_all (bind=engine,checkfirst=True), where you can specify the object to be deleted with the help of the tables attribute

Example 3: 5 constraints

3. 1 primary key can be done in either of the following two ways, one at the column level One is table-level In [7]: t_pk_col = Table ('id',Integer,primary_key=True), Column (' name',VARCHAR (20)) In [8]: t_pk_col.create (bind=engine) In [9]: t_pk_tb = Table ('id',Integer), Column (' name',VARCHAR (20)), PrimaryKeyConstraint ('id','name') Name='prikey') In [10]: t_pk_tb.create (bind=engine) 3.2 ForeignKey In [13]: t_fk = Table ('id',Integer,ForeignKey (' tweepk.id')) In [14]: t_fk.create (bind=engine) In [15]: t_fk_tb = Table ('col1',Integer), Column (' col2',VARCHAR (10)), ForeignKeyConstraint (['col1' (10)) In [16]: t_fk_tb.create (bind=engine) 3.3 unique In [17]: t_uni = Table ('id',Integer,unique=True) In [18]: t_uni.create (bind=engine) In [19]: t_uni_tb = Table (' col1',Integer), Column ('col2',VARCHAR (10)) UniqueConstraint ('col1','col2') In [20]: t_uni_tb.create (bind=engine) 3.4 check was successful However, MySQL does not currently support check constraints. There are no examples here. 3.5 not null In [21]: t_null = Table ('id',Integer,nullable=False) In [22]: t_null.create (bind=engine)

4 default value

There are two categories: pessimism (values provided by DB Server) and optimism (values provided by SQLAlshemy). Optimism can be divided into two categories: insert and update

Example: insert In [23]: t_def_inser = Table ('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 ('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 ('id',Integer), Column (' name',VARCHAR (10), DefaultClause ('cc')) In [28]: t_def_pass.create (bind=engine)

㈢ hides Schema

Whether the security of data is exposed to fully trusted objects is a risk that no security-conscious DBA will take. A better way is to hide the Schema structure as much as possible and verify the data integrity of the user input, which to a certain extent increases the cost of operation and maintenance, but the security is no small matter.

This problem is illustrated here by developing a command line tool.

Requirements: hide the table structure, implement dynamic query, and simulate the results of mysql\ G output

Version: [root@DataHacker ~] #. / sesc.py-- version 1.0View help: [root@DataHacker ~] #. / sesc.py-h Usage: sesc.py [options] [...] 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-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 look at the code

#! / usr/bin/env pythonimport optparsefrom dbapi import * # construct an OptionParser instance and configure the desired option parser = optparse.OptionParser (usage= "% prog [options] [...]", version='1.0',) # define command line options Use add_option to add one 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") 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 destination value to our custom variable table = options.tablecolumn = options.colterm = options.termformat = options.format# to achieve dynamic read query statement = "select * from% s where% s like'% s'"% (table,column Term) cur = conn.cursor () cur.execute (statement) results = cur.fetchall () # Analog\ G output form 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 + 1else: output = [] for record in xrange (0Len (results): output.append (results [record]) output = '.join (output) # directs 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 cursor and connect conn.close () cur.close ()

MySQL database through Python to operate Schema detailed steps to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.