安装mysql驱动

pip install pymysql

连接数据库

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","user","passwd","DBname" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

建表

import pymysql

db = pymysql.connect("localhost","root","123456","test" )
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS employee")
sql = """CREATE TABLE `employee` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` char(20) NOT NULL,
  `last_name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `income` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""

cursor.execute(sql)
print("Created table Successfull.")
db.close()

插入

import pymysql

db = pymysql.connect("localhost","root","123456","test" )
cursor = db.cursor()
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Su', 20, 'M', 5000)"""
try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()

## 再次插入一条记录
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Kobe', 'Bryant', 40, 'M', 8000)"""
try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()
print (sql)
print('Yes, Insert Successfull.')

db.close()

查询

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

• fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
• fetchall(): 接收全部的返回结果行.
• rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

以下过程查询EMPLOYEE表中所有记录的工资超过1000员工记录信息

import pymysql

db = pymysql.connect("localhost","root","123456","test" )
cursor = db.cursor()
# 按字典返回 
# cursor = db.cursor(pymysql.cursors.DictCursor)

sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %d" % (1000)
try:
   cursor.execute(sql)
   results = cursor.fetchall()
   for row in results:
      fname = row[1]
      lname = row[2]
      age = row[3]
      sex = row[4]
      income = row[5]
      print ("name = %s %s,age = %s,sex = %s,income = %s" % \
             (fname, lname, age, sex, income ))
except:
   import traceback
   traceback.print_exc()

   print ("Error: unable to fetch data")

db.close()

更新

以下程序将所有SEX字段的值为“M”的记录的年龄(age字段)更新为增加一年

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )
cursor = db.cursor()

sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 \
                          WHERE SEX = '%c'" % ('M')
try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()

db.close()

删除

以下是删除EMPLOYEE中AGE超过40的所有记录的程序

import pymysql

db = pymysql.connect("localhost","root","123456","test" )

cursor = db.cursor()

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (40)
try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()
db.close()