Two Methods for updating database scripts in Python and two methods for python

Two Methods for updating database scripts in Python and two methods for python

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

Import MySQLdb # HOST name = '20180101. 0.0.1 '# USER name = "root" # password PASSWD = "123456" # Database Name DB = "db_name" # Open the database connection db = MySQLdb. connect (HOST, USER, PASSWD, DB) # obtain the operation cursor = db. cursor () if _ name _ = '_ main _': if cursor: command_a = "update tables_one set status = 5 where status = 0" # Use the execute method to execute the SQL statement cursor.exe cute (command_a) # submit it to the database to execute the db. commit () command2 = "select field from tables_one where id = 12" ret2 = cursor.exe cute (command2) # obtain the ret2 = cursor list of all records. fetchall () for item in ret2: command3 = "insert into tables_two (name) values (% s);" % (item [0]) finerecursor.exe cute (command3) db. commit () # disable database connection to db. close ()

Database query methods

• Fetchone (): This method gets the next query result set. The result set is an object.
• Fetchall (): receives all returned result rows.
• 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.

#-*-Coding: UTF-8-*-from flask import Flaskfrom flask_sqlalchemy import SQLAlchemyfrom sqlalchemy. SQL import textHOST = '2017. 0.0.1 'user = "root" PASSWD = "123456" DB = "carrier_test" CHARTSET = "utf8" app = Flask (_ name __, instance_relative_config = True) # link to the database Path app. config ['sqlalchemy _ DATABASE_URI '] = 'mysql: // % s: % s@127.0.0.1: 3306/% s? Charset = % s' % (USER, PASSWD, DB, CHARTSET) # 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. App. config ['sqlalchemy _ TRACK_MODIFICATIONS '] = True # if it is set to True, SQLALCHEMY records all statements sent to the standard output (stderr), which is helpful for debugging. App. config ['sqlalchemy _ ECHO '] = False # size of the database connection pool. The default value is the default value of the Database Engine (usually 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 (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 _ 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 (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. iddef db_commit_all (lists ): Try: db. session. add_all (lists) db. session. commit () return 'success 'failed t Exception, e: return 'fail !!! 'Def commits_to_three_judge (): com_sta_obj = Table_one.query.filter_by (com_name = 'only used for testing, do not care about the relationship between table '). 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 _ ': # Run the database update function 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.

Summary

The above is the Python update database script method introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!