In the two iterations of the latest project, the database needs to be updated according to the changes of business requirements, and two iterations are updated in different ways.

The first is to use python's MySQLdb module to update with native sql statements

 1 import MySQLdb
 2 #host name
 3 HOST = '127.0.0.1'
 4 #User name
 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 # Get the 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         # Use execute Method execution SQL Sentence
20         cursor.execute(command_a)
21         # Submit to database for execution
22         db.commit()
23 
24         command2 = "select field from tables_one where id =12"
25         ret2 = cursor.execute(command2)
26         # Get a list of all records
27         ret2=cursor.fetchall()
28         for item in ret2:
29                 command3 = "insert into tables_two(name) values (%s);" % (item[0])
30                 fin=cursor.execute(command3)
31                 db.commit()
32         # Close database connection
33         db.close()

Three Ways of Database Query

• fetchone(): This method obtains the next query result set. The result set is an object
• fetchall(): Receives all returned result rows.
• rowcount: This is a read-only property and returns the number of rows affected by the execution of the execute() method.

Second: update using python's framework flask and sqlalchemy

 1 # -*- coding:utf-8 -*-
 2 from flask import Flask
 3 from flask_sqlalchemy import SQLAlchemy
 4 from sqlalchemy.sql import text
 5 
 6 HOST = '127.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 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 set to True (Default)，Flask-SQLAlchemy The modification of the object is tracked and the signal is sent. This requires extra memory and can be disabled if it is not necessary.
16 app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
17 #If set to True，SQLAlchemy Records will be sent to standard output(stderr)This is very helpful for debugging.
18 app.config['SQLALCHEMY_ECHO'] = False
19 # The size of the database connection pool. The default is the default value of the database engine (usually 5).
20 app.config['SQLALCHEMY_POOL_SIZE'] = 6
21 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>' % self.com_name
32 
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         return '<table_two id %r>' % self.id
44 
45 def db_commit_all(lists):
46     try:
47         db.session.add_all(lists)
48         db.session.commit()
49         return 'SUCCESS'
50     except Exception,e:
51         return 'Fail!!!'
52 
53 def commits_to_three_judge():
54     com_sta_obj = Table_one.query.filter_by(com_name='Just test usage,Don't care about the relationship between tables').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 = 0
60                 print db_commit_all([ite])
61     print 'Table Synchronization End'
62 
63 64 
65 if __name__=='__main__':
66     #Perform update database functions
67     commits_to_three_judge()

Two ways to compare:

1. In practical projects, database updates need to use a lot of correlation functions to collect data and determine whether the conditions are met. These correlation functions are all used in the project to perform data-related operations, such as db_commit_all() function in the second method.

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

3. If flask is used for development in the project, the second method is recommended for database updating.

Configure Links in Flask-SQL Alchemy  http://www.pythondoc.com/flask-sqlalchemy/config.html

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