There are two ways in Python to implement an example of updating a database script

In the two iterations of the recent project, the database needs to be updated, two times in different ways, depending on the changes in business requirements.

First: Use Python's mysqldb module to update with native SQL statements

Import mysqldb# hostname host = ' 127.0.0.1 ' #用户名USER = "root" #密码PASSWD = "123456" #数据库名DB = "db_name" # Open database connection Db=mysqldb.connect ( HOST,USER,PASSWD,DB) # Get Operation Cursor cursor=db.cursor () if __name__ = = ' __main__ ':    if cursor:        command_a = "Update tables _one set status=5 where status=0 "        # Execute SQL statement using Execute method        Cursor.execute (command_a)        # Commit to Database execution        Db.commit ()        command2 = "Select field from Tables_one where ID =12"        ret2 = Cursor.execute (command2)        # get all record columns Table        Ret2=cursor.fetchall () for        item in Ret2:                command3 = "INSERT into Tables_two (name) values (%s);"% (item[ 0])                fin=cursor.execute (Command3)                db.commit ()        # Close database connection        db.close ()

Database query Three Ways

• Fetchone (): This method gets the next query result set. The result set is an object

• Fetchall (): receives all the returned result rows.

• ROWCOUNT: This is a read-only property and returns the number of rows affected after the Execute () method is executed.

Second: Update using Python's framework flask and SQLAlchemy

#-*-Coding:utf-8-*-from flask Import flaskfrom flask_sqlalchemy import sqlalchemyfrom sqlalchemy.sql Import TextHOST = ' 127.0.0.1 ' USER = ' root ' PASSWD = ' 123456 ' DB = ' carrier_test ' chartset = ' utf8 ' app = Flask (__name__,instance_relative_ Config = True) #链接数据库路径app. config[' sqlalchemy_database_uri ' = ' mysql://%s:%s@127.0.0.1:3306/%s?charset=%s '% (USER, Passwd,db,chartset) #如果设置成 True (by default), Flask-sqlalchemy will track the modification of the object and send a signal. This requires additional memory and can be disabled if it is not necessary. app.config[' sqlalchemy_track_modifications ' = true# If set to True,sqlalchemy will record all statements sent to standard output (stderr), which is helpful for debugging. app.config[' Sqlalchemy_echo ' = false# the size of the database connection pool. The default is the default value of the database engine (typically 5). app.config[' sqlalchemy_pool_size ' = 6db = 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 (+), nullable=false) Com_about = db. Column (' Com_about ', db. String ($), nullable=false) def __repr__ (self): return ' <table_onE com_name%r> '% self.com_nameclass table_two (db. Model): __tablename__ = ' table_two ' id = db. Column (' id ', db. Integer, primary_key=true, autoincrement=true) reason = db. Column (' reason ', db. String (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.iddef db_commit_ All (lists): Try:db.session.add_all (lists) Db.session.commit () return ' SUCCESS ' except exceptio N,e:return ' Fail!!! ' Def commits_to_three_judge (): Com_sta_obj = Table_one.query.filter_by (com_name= ' is just for testing use, don't care about inter-table relationships '). All () for ITE in C Om_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 ' Table Sync end ' if __name__== ' __m Ain__ ': #Perform update database function Commits_to_three_judge () 

Two ways to compare:

1. In the actual project, the database updates need to use many related functions for data collection, to determine whether the conditions are satisfied, and these related functions are used in the project SQLAlchemy data-related operations, such as the second method of the Db_commit_all () function

2. Using the second method, directly copy these functions into the script, if you use the first method, you need to rewrite the relevant functions, increase development time, wasted effort.

3. If the project is developed using flask, the second method is recommended for database updates.