[大数据]Python操作Mysql例子

Mysql是常用的数据库，对应Python3库名为PyMySQL；

# 登录MySQL
mysql -u root -p
# 显示数据库
SHOW DATABASES;
# 如果存在test_db数据库则删除
DROP DATABASE IF EXISTS test_db;
# 创建数据库test_db
CREATE DATABASE IF NOT EXISTS test_db 
       DEFAULT CHARACTER SET utf8 
       DEFAULT COLLATE utf8_general_ci;

创建好数据库之后就可以使用Python来连接；

import pymysql
# 连接数据库
myconn = pymysql.connect(host='localhost',user='root',password='12345678',
                         database='test_db',charset='utf8')                         
# 创建游标对象
mycursor = myconn.cursor()

然后创建一张数据表好向里面添加数据；

# 如果存在对应表名则删除表
sql = '''DROP TABLE IF EXISTS playmates_tbl;'''
mycursor.execute(sql)
# 创建数据表以及对应字段属性
sql = '''CREATE TABLE IF NOT EXISTS `playmates_tbl`
         (`mates_id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
          `mates_name` VARCHAR(100) NOT NULL,
          `mates_show` VARCHAR(40) NOT NULL,
          `mates_level` VARCHAR(40) NOT NULL,
          `submission_date` DATE)
         ENGINE = InnoDB DEFAULT CHARSET = utf8;'''    
mycursor.execute(sql)

然后向创建好的MySQL数据表里增加数据；

infolist = [{'name':'爱飞的鱼3306','show':'1314','level':'黄金II'},
            {'name':'黑陶K','show':'1208','level':'钻石III'}]
sql = '''INSERT INTO playmates_tbl 
         (mates_name, mates_show, mates_level, submission_date)
         VALUES 
         (%s, %s, %s, NOW());'''
for player in infolist:
    rollback = mycursor.execute(sql,[player['name'],player['show'],player['level']])
    print(rollback)

然后查询对应数据表中的数据看是否符合预期；

sql = '''SELECT * from playmates_tbl;'''
rollback = mycursor.execute(sql)
print(rollback)
results = mycursor.fetchall()
for row in results:
    print(row)

之后可以对表中的数据进行修改和删除；

# 修改数据表中对应名称的数据
sql = '''UPDATE playmates_tbl SET mates_show = "1602" WHERE mates_name = '黑陶K';'''
rollback = mycursor.execute(sql)
print(rollback)
# 删除数据表中对应ID的数据
sql = '''DELETE FROM playmates_tbl WHERE mates_id=2;'''
rollback = mycursor.execute(sql)
print(rollback)
# 最后提交和关闭连接
myconn.commit()
myconn.close()