python操作数据库总结记录

╰半夏微凉° 2021-11-24 01:42 382阅读 0赞

### 1.python查询数据库 ###

1.1 数据库表

CREATE TABLE `test` (
      `test` varchar(255) NOT NULL,
      `last_total_money` bigint(32) DEFAULT NULL,
      `last_allvip_number` int(16) DEFAULT NULL COMMENT '昨天新增vip数量',
      `type` int(2) DEFAULT '0' COMMENT '1表示社群销售发短信，0表示普通统计',
      PRIMARY KEY (`test`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

![2019072713530023.png][]

1.2 python执行数据库查询代码

#!/usr/bin/python
    # -*- coding: UTF-8 -*-
    
    import pymysql
    db = pymysql.connect("127.0.0.1", "root", "123456", "TESTDB", charset='utf8' )
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sql='SELECT * from test'
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 获取所有记录列表
       results = cursor.fetchall()
       for row in results:
          test = row[0]
          last_total_money = row[1]
          # 打印结果
          print("test=%s,last_total_money=%s" % \
                 (test,last_total_money))
    except:
       print("Error: unable to fecth data")
       #关闭数据库连接
    db.close()

1.3  执行查询结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhbnpsMQ_size_16_color_FFFFFF_t_70][]

### 2.python查询数据库单条 ###

--------------------

import pymysql
    db = pymysql.connect("127.0.0.1", "root", "123456", "TESTDB", charset='utf8' )
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sql='select * from test where test=%s'%1
    
    cursor.execute(sql)
    
    data=cursor.fetchone()
    if data!=None:
        print(data)
       #关闭数据库连接
    db.close()

2.1执行结果

![20190806150913348.png][]

### 3.python 插入数据 ###

import pymysql
    db = pymysql.connect("127.0.0.1", "root", "123456", "TESTDB", charset='utf8' )
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sql = """INSERT INTO test(test,last_total_money,last_allvip_number,type)
             VALUES (4,100,30,3)"""
    try:
       # 执行sql语句
       cursor.execute(sql)
       # 提交到数据库执行
       db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()
    
    # 关闭数据库连接
    db.close()

更新，删除操作同插入，只需要修改相应的sql即可

[2019072713530023.png]: /images/20211122/96635a96a43047639e12aa67d7070968.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhbnpsMQ_size_16_color_FFFFFF_t_70]: /images/20211122/493b4bc862194a6a8a2f63a0add5062c.png
[20190806150913348.png]: /images/20211122/7bc0f98c21df4c5398c5f9c2bdb031a3.png