Python MySQL部分操作

 

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接host="",user="",passwd="",db=""，charset="utf8"
db = MySQLdb.connect("192.76.120.17", "hegongda", "123456", "class", charset='utf8' )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print("Database version : %s " % data)

########删除数据库#######
# SQL 删除语句
sql = "DELETE FROM user where user_name='小岳' " 
sql1 = "DELETE FROM behavior_recognition " 
sql2 = "DELETE FROM face_recognition "
try:
   # 执行SQL语句
   cursor.execute(sql)
   cursor.execute(sql1)
   cursor.execute(sql2)
   # 提交修改
   db.commit()
   print("delete OK")                  
except:
   # 发生错误时回滚
   db.rollback()

########写入表1#######
 # 提交到数据库执行,sql = "INSERT INTO 表名(id, quanwen) VALUES (%d, \'%s\')" % (i, eachline)    i和 eachline可以为变量名
text = str(picnum) + '.jpg'
sql1 = """INSERT INTO behavior(id,behavior_name) VALUES ('%s', '%d')"""% (text, cls_id)
try:
   # 执行sql语句
   cursor.execute(sql1)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   print("Error: unable to insert data")
   db.rollback()
####################


########写入表2#########
sql2 = """INSERT INTO user(id,user_name,user_number,sex) VALUES (1,'**',10010,男)"""
try:
   # 执行sql语句
   cursor.execute(sql2)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   print("Error: unable to insert data")
   db.rollback()
#######################


########写入表3#########
sql3 = """INSERT INTO behavior_recognition(id,image_path,behavior_id,record_time) VALUES (18,'20190321181134.jpg',8,'2019-03-23 14:35:48')"""
try:
   # 执行sql语句
   cursor.execute(sql3)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   print("Error: unable to insert data")
   db.rollback()
#######################


#######写入表4#########
sql4 = """INSERT INTO face_recognition(id,image_path,user_id,record_time) VALUES (19,'20190321181134.jpg',8,'2019-03-23 14:35:48')"""
try:
   # 执行sql语句    
   cursor.execute(sql4)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   print("Error: unable to insert data")
   db.rollback()
#######################


#######查询表1##########
sql1_query = """SELECT * FROM behavior""" 
try:
   # 执行SQL语句
   cursor.execute(sql1_query)
   # 获取所有记录列表
   results = cursor.fetchall()

   for row in results:
      behavior_id = row[0]
      behavior_name = row[1]

      # 打印结果
      print("behavior_id : %s " % behavior_id)
      print("behavior_name : %s " % behavior_name)
except:
   print("Error: unable to fecth data")
########################


#######查询表2###########
sql2_query = """SELECT * FROM user""" 
try:
   # 执行SQL语句
   cursor.execute(sql2_query)
   # 获取所有记录列表
   results = cursor.fetchall()

   for row in results:
      user_id = row[0]
      user_name = row[1]
      user_number = row[2]
      user_sex = row[3]
      # 打印结果
      print("user_id : %s " % user_id)
      print("user_name : %s " % user_name)
      print("user_number : %s " % user_number)
      print("user_sex : %s " % user_sex)
except:
   print("Error: unable to fecth data")
#########################


#######查询表3###########
sql3_query = """SELECT * FROM behavior_recognition""" 
try:
   # 执行SQL语句
   cursor.execute(sql3_query)
   # 获取所有记录列表
   results = cursor.fetchall()

   for row in results:
      behavior_recognition_id = row[0]
      behavior_recognition_image_path = row[1]
      behavior_recognition_behavior_id = row[2]
      behavior_recognition_record_time = row[3]
      # 打印结果
      print("behavior_recognition_id : %s " % behavior_recognition_id)
      print("behavior_recognition_image_path : %s " % behavior_recognition_image_path)
      print("behavior_recognition_behavior_id : %s " % behavior_recognition_behavior_id)
      print("behavior_recognition_record_time : %s " % behavior_recognition_record_time)
except:
   print("Error: unable to fecth data")
#########################

#######查询表4###########
sql4_query = """SELECT * FROM face_recognition""" 
try:
   # 执行SQL语句
   cursor.execute(sql4_query)
   # 获取所有记录列表
   results = cursor.fetchall()

   for row in results:
      face_recognition_id = row[0]
      face_recognition_image_path = row[1]
      face_recognition_user_id = row[2]
      face_recognition_record_time = row[3]
      # 打印结果
      print("face_recognition_id : %s " % face_recognition_id)
      print("face_recognition_image_path : %s " % face_recognition_image_path)
      print("face_recognition_user_id : %s " % face_recognition_user_id)
      print("face_recognition_record_time : %s " % face_recognition_record_time)
except:
   print("Error: unable to fecth data")
#########################


# 关闭数据库连接
db.close()

可以参考：https://www.jianshu.com/p/b5509ec6e76b

删除操作：https://blog.youkuaiyun.com/chengqiuming/article/details/78601255