Python update database script two methods and a comparative introduction

In the last two iterations of the project, the database needed to be updated as business requirements changed, and the two updates were done in different ways.

Type 1: The MySQLdb module using python is updated with native sql statements

import MySQLdb
# The host name 
HOST = '127.0.0.1'
# The user name 
USER = "root"
# password 
PASSWD = "123456"
# The database name 
DB = "db_name"
#  Open database connection 
db=MySQLdb.connect(HOST,USER,PASSWD,DB)
#  Fetch operation cursor 
cursor=db.cursor()
if __name__ == '__main__':
  if cursor:
    command_a = "update tables_one set status=5 where status=0"
    #  use execute Methods to perform SQL statements 
    cursor.execute(command_a)
    #  Commit to the database for execution 
    db.commit()
    command2 = "select field from tables_one where id =12"
    ret2 = cursor.execute(command2)
    #  Gets a list of all records 
    ret2=cursor.fetchall()
    for item in ret2:
        command3 = "insert into tables_two(name) values (%s);" % (item[0])
        fin=cursor.execute(command3)
        db.commit()
    #  Close the database connection 
    db.close()

Database query in 3 ways

fetchone(): This method gets the next query result set. The result set is 1 object fetchall(): Receives all result rows returned. rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method.

Type 2: Update using python's frameworks flask and sqlalchemy

# -*- coding:utf-8 -*-
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import text
HOST = '127.0.0.1'
USER = "root"
PASSWD = "123456"
DB = "carrier_test"
CHARTSET = "utf8"
app = Flask(__name__,instance_relative_config = True)
# Linked database path 
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://%s:%s@127.0.0.1:3306/%s?charset=%s' %(USER,PASSWD,DB,CHARTSET)
# If I set it to  True ( The default ) . Flask-SQLAlchemy  Changes to the object are tracked and a signal is sent. This requires extra memory,   Disable it if it's not necessary. 
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# If I set it to  True . SQLAlchemy  All will be recorded   Send to Standard output (stderr) Statement, which is very helpful for debugging. 
app.config['SQLALCHEMY_ECHO'] = False
#  Database connection pool size. The default is the default value for the database engine   (usually  5 ). 
app.config['SQLALCHEMY_POOL_SIZE'] = 6
db = SQLAlchemy(app)
class Table_one(db.Model):
  __tablename__ = 'table_one'
  id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
  com_name = db.Column('com_name', db.String(30), nullable=False)
  com_about = db.Column('com_about', db.String(200), nullable=False)
  def __repr__(self):
    return '<table_one com_name %r>' % self.com_name
class Table_two(db.Model):
  __tablename__ = 'table_two'
  id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
  reason = db.Column('reason', db.String(128), nullable=True)
  create_time = db.Column('create_time', db.TIMESTAMP, server_default=text('now()'))
  status = db.Column('status', db.Integer, nullable=False, default=0)
  def __repr__(self):
    return '<table_two id %r>' % self.id
def db_commit_all(lists):
  try:
    db.session.add_all(lists)
    db.session.commit()
    return 'SUCCESS'
  except Exception,e:
    return 'Fail!!!'
def commits_to_three_judge():
  com_sta_obj = Table_one.query.filter_by(com_name=' It's just test use , You don't have to worry about the relationships between the tables ').all()
  for ite in com_sta_obj:
    ship_obj = Table_two.query.filter_by(id=ite.id).first()
    if ship_obj:
      if int(ship_obj.status) == 2:
        ite.status = 0
        print db_commit_all([ite])
  print ' End of table synchronization '
64 
if __name__=='__main__':
  # Perform the update database function 
  commits_to_three_judge()

Two ways to compare:

1. In the actual project, database update needs to use many related functions to collect data and determine whether conditions are met, etc. These related functions in the project all use Sqlalchemy for data related operation, such as db_commit_all() function in the second method

2. Use the second method and directly copy these functions into the script. If you use the first method, you need to rewrite 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 update.

conclusion