python连接数据库，笔记

1、创建表

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

2、插入

#!/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)

# 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()

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