python连接数据库，笔记

港控/mmm° 2022-04-25 08:22 303阅读 0赞

## 1、创建表 ##

我是直接在可视化界面创建的数据库和表单

2、插入

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70][]

![20190530175601492.png][]

#!/usr/bin/env python
    # -*- coding: utf-8 -*-
    # @File  : test.py
    # @Author: DianaZhang
    # @Date  : 2019/5/29
    import json
    import pymysql as MySQLdb
    
    db = MySQLdb.connect("60.191.110.43", "develop", "dev123", "company_information", charset='utf8')
    
    cursor = db.cursor()
    
    sql = """INSERT INTO `company_test` (`id`, `name`, `city`) VALUES ('6', '李四', '四川')"""
    # 成功提交
    sql = """INSERT INTO `company_test` (`id`, `name`, `city`) VALUES ('{}','{}','{}')""".format(input_name['id'],input_name['name'],input_name['city'])
    # 批量插入
    
    cursor.execute(sql)
    
    cursor.connection.commit()#执行commit操作，插入语句才能生效
    
    db.close()

划重点 一定要加上“cursor.connection.commit()”这句话，否则执行成功了，也无法插入成功

# 建表
    # 删除表。
    cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
    
    # 创建数据表
    sql = """CREATE TABLE EMPLOYEE (
             FIRST_NAME  CHAR(20) NOT NULL,
             LAST_NAME  CHAR(20),
             AGE INT,  
             SEX CHAR(1),
             INCOME FLOAT )"""
    
    cursor.execute(sql)

![20190530112123888.png][]

# SQL 更新语句
    # sql = "UPDATE btable SET prov = beijing WHERE  id = 2"
    # 上面这句话是错误的，因为beijing没有用引号引起来
    sql = "UPDATE btable SET peov = 'beijing' WHERE  id = '2'"
    
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 提交到数据库执行
       db.commit()
    except:
       # 发生错误时回滚
       db.rollback()

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70 1][]

# 打开远程数据库
    db = MySQLdb.connect("192.168.XXX.XXX", "develop", "dev123", "company_information", 3306, charset='utf8' )

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70]: /images/20220125/bef635b84f844bdd8991fd1a271f6ec2.png
[20190530175601492.png]: /images/20220125/ac9f91eaafc642e1af4e316e384160d7.png
[20190530112123888.png]: /images/20220125/1bdf0fbfce26439a9d6ba998ab004ed8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70 1]: /images/20220125/f2d45ba5e95d401f979082260da223a5.png