Python语法之PyMySQL思维导图

核心功能代码:

import io 
import sys 
import pymysql 
  
sys.stdout = io.TextIOWrapper(sys.stdout.detach(),encoding='utf-8') 
 
# 打开数据连接 
conn = pymysql.connect("localhost","root","123456","mysql") 
# 创建游标对象 
cursor = conn.cursor() 
# 游标执行sql 查询 
sql = "select version()" 
# 执行sql 查询 
cursor.execute(sql) 
# 读取结果 
data = cursor.fetchone() 
print("Database version:%s" % data) 
#关闭游标 
cursor.close() 
#数据库关闭 
conn.close() 
 
 
# 打开数据连接 
conn = pymysql.connect("localhost","root","123456","mysql") 
# 创建游标对象 
cursor = conn.cursor() 
# 使用 execute() 方法执行 SQL，如果表存在则删除 
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 )""" 
# 执行sql 语句 
cursor.execute(sql) 
#关闭游标 
cursor.close() 
#数据库关闭 
conn.close() 
 
 
# 打开数据库连接 
conn = pymysql.connect("localhost","root","123456","mysql") 
  
# 使用cursor()方法获取操作游标  
cursor = conn.cursor() 
  
# SQL 插入语句 
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, 
         LAST_NAME, AGE, SEX, INCOME) 
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" 
try: 
   # 执行sql语句 
   cursor.execute(sql) 
   # 提交到数据库执行 
   conn.commit() 
except: 
   # 如果发生错误则回滚 
   conn.rollback() 
  
# 关闭数据库连接 
conn.close() 
 
# 打开数据库连接 
conn = pymysql.connect("localhost","root","123456","mysql") 
  
# 使用cursor()方法获取操作游标  
cursor = conn.cursor() 
  
# SQL 查询语句 
sql = "SELECT * FROM EMPLOYEE \ 
       WHERE INCOME > %s" % (1000) 
try: 
   # 执行SQL语句 
   cursor.execute(sql) 
   # 获取所有记录列表 
   results = cursor.fetchall() 
   for row in results: 
      fname = row[0] 
      lname = row[1] 
      age = row[2] 
      sex = row[3] 
      income = row[4] 
       # 打印结果 
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ 
             (fname, lname, age, sex, income )) 
except: 
   print ("Error: unable to fetch data") 
  
# 关闭数据库连接 
conn.close() 
 
 
# 打开数据库连接 
conn = pymysql.connect("localhost","root","123456","mysql") 
  
# 使用cursor()方法获取操作游标  
cursor = conn.cursor() 
  
# SQL 删除语句 
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (10) 
try: 
   # 执行SQL语句 
   cursor.execute(sql) 
   # 提交修改 
   conn.commit() 
except: 
   # 发生错误时回滚 
   conn.rollback() 
  
# 关闭连接 
conn.close()