python：mysql之UPDATE更新

接下来。装逼开始....

Update 更新用于修改表中的数据。

语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

语法中可以看到，能同时更新多个字段，也能使用WHERE子句并跟随指定条件...

示例1：终端执行sql语句修改数据

更新python的地址信息【更新某一行中的一个列】

root@7c6316b19d80:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 147
Server version: 5.6.51 MySQL Community Server (GPL)

mysql> select * from test_user where name = 'python';
+----+-----------+-------------+--------------+
| id | name      | mobile      | address      |
+----+-----------+-------------+--------------+
|  3 | python    | 18856565858 | Hangzhou     |
+----+-----------+-------------+--------------+
6 rows in set (0.00 sec)

mysql> update test_user set address = 'Hefei' where name = 'python';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_user where name = 'python';
+----+-----------+-------------+--------------+
| id | name      | mobile      | address      |
+----+-----------+-------------+--------------+
|  3 | python    | 18856565858 | Hefei        |
+----+-----------+-------------+--------------+
6 rows in set (0.00 sec)

mysql>

更新c#的手机号和地址信息【更新某一行中的若干列，记得逗号分割】

mysql> select * from test_user where name = 'c#';
+----+-----------+-------------+--------------+
| id | name      | mobile      | address      |
+----+-----------+-------------+--------------+
|  6 | c#        | 17748484142 | Hangzhou     |
+----+-----------+-------------+--------------+
6 rows in set (0.00 sec)

mysql> update test_user set address = 'Hefei', mobile = '19958585454' where name = 'c#';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_user where name = 'c#';
+----+-----------+-------------+--------------+
| id | name      | mobile      | address      |
+----+-----------+-------------+--------------+
|  6 | c#        | 19958585454 | Hefei        |
+----+-----------+-------------+--------------+
6 rows in set (0.00 sec)

mysql>

示例2：使用python脚本执行sql语句

更新张三三的地址信息【更新某一行中的一个列】

import pymysql

# 连接数据库
connection = pymysql.connect(host="localhost", user="root", password="123456",
                             database="testing", port=3306, charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection:
        with connection.cursor() as cursor:
            sql = """
                UPDATE test_user
                SET address = '安徽合肥'
                WHERE
                    `name` = '张三三';
            """
            # 执行创建sql语句
            cursor.execute(sql)
            # 提交数据
        connection.commit()

        with connection.cursor() as cursor:
            sql = """
                SELECT
                    *
                FROM
                    test_user
                WHERE
                    `name` = '张三三';
            """
            cursor.execute(sql)
            result = cursor.fetchone()
            print(result)

except pymysql.err.MySQLError as _error:
    raise _error

{'id': 1, 'name': '张三三', 'mobile': '17748484141', 'address': '安徽合肥'}

Process finished with exit code 0

上述代码中可以看到，更新和查询都在一起，当更新和查询数据一致时操作是没问题的，但是如果我只更新数据不查询数据时，虽然不会报错，但是查询的代码还是会执行下，所以代码要优化...

代码区分开了查询和提交，查询中区分开了获取多条数据和单条数据

如果数据类型是要字典格式的，就可以使用下面的通用代码... 当然，如果你是想要别的数据类型，修改下返回数据类型即可...

import pymysql


class MysqlData:
    def __init__(self, host="localhost", user="root", password="123456", database="testing", port=3306):
        # 连接数据库
        self.connection = pymysql.connect(host=host, user=user, password=password, database=database, port=port,
                                          charset='utf8', cursorclass=pymysql.cursors.DictCursor)

    def modify_data(self, sql, args=None):
        """

        Args:
            sql:    sql语句
            args:   接收格式化参数

        Returns:

        """
        try:
            with self.connection:
                with self.connection.cursor() as cursor:
                    # 执行sql语句
                    cursor.execute(sql, args=args)
                # 提交sql语句
                self.connection.commit()
        except pymysql.err.MySQLError as _error:
            raise _error

    def get_data(self, sql, args=None, is_data=False):
        """

        Args:
            sql:        sql语句
            args:       接收格式化参数
            is_data:    判断获取多条数据还是单个数据，默认获取单个数据

        Returns:

        """
        try:
            with self.connection:
                with self.connection.cursor() as cursor:
                    # 执行sql语句
                    cursor.execute(sql, args=args)

                    # 返回查询出的数据信息
                    return cursor.fetchall() if is_data else cursor.fetchone()

        except pymysql.err.MySQLError as _error:
            raise _error
            

if __name__ == '__main__':
    # 初始化类，创建对象
    info = MysqlData()

    # 调用查询方法并打印出来
    print(info.get_data("SELECT * FROM test_user WHERE `name` = 'php'"))

    # 调用更新修改方法：修改php的地址和手机号【更新某一行的若干列，并且逗号分割】
    info.modify_data("UPDATE test_user SET address = '安徽合肥', mobile = '17752525151' WHERE `name` = 'php';")

    # 再次调用查询方法并打印出来
    print(info.get_data("SELECT * FROM test_user WHERE `name` = 'php'"))

{'id': 5, 'name': 'php', 'mobile': '15556565858', 'address': 'Hangzhou'}
{'id': 5, 'name': 'php', 'mobile': '17752525151', 'address': '安徽合肥'}

Process finished with exit code 0

至此，UPDATE更新数据完成...

 

以上总结或许能帮助到你，或许帮助不到你，但还是希望能帮助到你，如有疑问、歧义，直接私信留言会及时修正发布；感觉还不错记得点赞呦，谢谢！

未完，待续…

一直都在努力，希望您也是！

微信搜索公众号：就用python

更多内容欢迎关注公众号