Python updates database scripts in two ways, python in two ways

Python updates database scripts in two ways, python in two ways

In the two version iterations of the recent project, the database needs to be updated based on changes in business needs. The two iterations use different methods for updates respectively.

First: Use python's MySQLdb module to update with native SQL statements

1 import MySQLdb 2 # HOST Name 3 HOST = '2017. 0.0.1 '4 # username 5 USER = "root" 6 # password 7 PASSWD = "123456" 8 # Database Name 9 DB = "db_name" 10 # Open Database Connection 11 db = MySQLdb. connect (HOST, USER, PASSWD, DB) 12 # obtain operation cursor 13 cursor = db. cursor () 14 15 if _ name _ = '_ main _': 16 17 if cursor: 18 command_a = "update tables_one set status = 5 where status = 0" 19 # execute SQL statement 20 cursor.exe cute (command_a) 21 # submit to the database for execution 22 db. commit () 23 24 command2 = "select field from tables_one where id = 12" 25 ret2 = cursor.exe cute (command2) 26 # retrieve all records list 27 ret2 = cursor. fetchall () 28 for item in ret2: 29 command3 = "insert into tables_two (name) values (% s);" % (item [0]) 30 finerecursor.exe cute (command3) 31 db. commit () 32 # Close database connection 33 db. close ()

Database query methods

• Fetchone ():This method gets the next query result set. The result set is an object.
• Fetchall ():Receives all returned results.
• Rowcount:This is a read-only attribute and returns the number of rows affected by execution of the execute () method.

 

Second: Use the python framework flask and sqlalchemy for updates.

1 #-*-coding: UTF-8-*-2 from flask import Flask 3 from flask_sqlalchemy import SQLAlchemy 4 from sqlalchemy. SQL import text 5 6 HOST = '2017. 0.0.1 '7 USER = "root" 8 PASSWD = "123456" 9 DB = "carrier_test" 10 CHARTSET = "utf8" 11 12 app = Flask (_ name __, instance_relative_config = True) 13 # link to the database path 14 app. config ['sqlalchemy _ DATABASE_URI '] = 'mysql: // % s: % s@127.0.0.1: 3306/% s? Charset = % s' % (USER, PASSWD, DB, CHARTSET) 15 # if it is set to True (default), Flask-SQLAlchemy will track the modification of the object and send signals. This requires additional memory, which can be disabled if necessary. 16 app. config ['sqlalchemy _ TRACK_MODIFICATIONS '] = True17 # if it is set to True, SQLALCHEMY records all statements sent to the standard output (stderr), which is helpful for debugging. 18 app. config ['sqlalchemy _ ECHO '] = False19 # size of the database connection pool. The default value is the default value of the Database Engine (usually 5 ). 20 app. config ['sqlalchemy _ POOL_SIZE '] = 621 db = SQLALCHEMY (app) 22 23 class Table_one (db. model): 24 _ tablename _ = 'table _ one' 25 26 id = db. column ('id', db. integer, primary_key = True, autoincrement = True) 27 com_name = db. column ('com _ name', db. string (30), nullable = False) 28 com_about = db. column ('com _ about', db. string (200), nullable = False) 29 30 def _ repr _ (self): 31 return '<table_one com_name % r>' % s Elf.com _ name32 33 34 class Table_two (db. model): 35 _ tablename _ = 'table _ two' 36 37 id = db. column ('id', db. integer, primary_key = True, autoincrement = True) 38 reason = db. column ('reason ', db. string (128), nullable = True) 39 create_time = db. column ('create _ time', db. TIMESTAMP, server_default = text ('now () ') 40 status = db. column ('status', db. integer, nullable = False, default = 0) 41 42 def _ repr _ (self): 43 re Turn '<table_two id % r>' % self. id44 45 def db_commit_all (lists): 46 try: 47 db. session. add_all (lists) 48 db. session. commit () 49 return 'success' 50 failed t Exception, e: 51 return 'fail !!! '52 53 def commits_to_three_judge (): 54 com_sta_obj = Table_one.query.filter_by (com_name = 'is only used for testing and does not care about the relationship between table '). all () 55 for ite in com_sta_obj: 56 ship_obj = Table_two.query.filter_by (id = ite. id ). first () 57 if ship_obj: 58 if int (ship_obj.status) = 2: 59 ite. status = 060 print db_commit_all ([ite]) 61 print 'end of table synchronization '62 63 64 65 if _ name __= = '_ main __': 66 # execute the update database function 67 commits_to_three_judge ()

 

Comparison:

1. in actual projects, database updates require a lot of related functions to collect data and determine whether the conditions are met. In the project, these functions use Sqlalchemy for data-related operations, for example, the db_commit_all () function in the second method

2. Use the second method to directly copy these functions to the script. If you use the first method, you need to rewrite the related functions to increase development time and waste energy.

3. If flask is used for development in the project, we recommend that you use the second method for database updates.

 

Configure related link http://www.pythondoc.com/flask-sqlalchemy/config.html in Flask-SQLAlchemy

Python uses MySQLdb to operate mysql database-related connection http://www.runoob.com/python/python-mysql.html