Examples of updating database scripts that can be implemented using two methods in python

In the two recent version iterations of the project, the database needed to be updated according to changes in business requirements, and different methods were used for the updates twice.

The first one: Use python’s MySQLdb module to update using native sql statements

import MySQLdb
#主机名
HOST = '127.0.0.1'
#用户名
USER = "root"
#密码
PASSWD = "123456"
#数据库名
DB = "db_name"
# 打开数据库连接
db=MySQLdb.connect(HOST,USER,PASSWD,DB)
# 获取操作游标
cursor=db.cursor()

if __name__ == '__main__':

    if cursor:
        command_a = "update tables_one set status=5 where status=0"
        # 使用execute方法执行SQL语句
        cursor.execute(command_a)
        # 提交到数据库执行
        db.commit()

        command2 = "select field from tables_one where id =12"
        ret2 = cursor.execute(command2)
        # 获取所有记录列表
        ret2=cursor.fetchall()
        for item in ret2:
                command3 = "insert into tables_two(name) values (%s);" % (item[0])
                fin=cursor.execute(command3)
                db.commit()
        # 关闭数据库连接
        db.close()

Three ways to query the database

• 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 property and returns the number of rows affected after executing the execute() method.

Second: Use python’s framework flask and sqlalchemy to update

# -*- 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)
#链接数据库路径
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://%s:%s@127.0.0.1:3306/%s?charset=%s' %(USER,PASSWD,DB,CHARTSET)
#如果设置成 True (默认情况)，Flask-SQLAlchemy 将会追踪对象的修改并且发送信号。这需要额外的内存， 如果不必要的可以禁用它。
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
#如果设置成 True，SQLAlchemy 将会记录所有 发到标准输出(stderr)的语句，这对调试很有帮助。
app.config['SQLALCHEMY_ECHO'] = False
# 数据库连接池的大小。默认是数据库引擎的默认值 （通常是 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 &#39;<table_one com_name %r>&#39; % self.com_name


class Table_two(db.Model):
    __tablename__ = &#39;table_two&#39;

    id = db.Column(&#39;id&#39;, db.Integer, primary_key=True, autoincrement=True)
    reason = db.Column(&#39;reason&#39;, db.String(128), nullable=True)
    create_time = db.Column(&#39;create_time&#39;, db.TIMESTAMP, server_default=text(&#39;now()&#39;))
    status = db.Column(&#39;status&#39;, db.Integer, nullable=False, default=0)

    def __repr__(self):
        return &#39;<table_two id %r>&#39; % self.id

def db_commit_all(lists):
    try:
        db.session.add_all(lists)
        db.session.commit()
        return &#39;SUCCESS&#39;
    except Exception,e:
        return &#39;Fail!!!&#39;

def commits_to_three_judge():
    com_sta_obj = Table_one.query.filter_by(com_name=&#39;只是测试使用,不用关心表间关系&#39;).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 &#39;表同步结束&#39;

64 
if __name__==&#39;__main__&#39;:
    #执行更新数据库函数
    commits_to_three_judge()

Comparison of two methods:

1. In actual projects, database updates require the use of many related functions to collect data, determine whether conditions are met, etc. These related functions use Sqlalchemy for data-related operations in the project, such as the db_commit_all() function in the second method

2. Using the second method, just copy these functions directly into the script. , If you use the first method, you need to rewrite related functions, which increases development time and wastes energy.

3. If the project is developed using flask, it is recommended to use the second method to update the database.

The above is the detailed content of Examples of updating database scripts that can be implemented using two methods in python. For more information, please follow other related articles on the PHP Chinese website!