MYSQL:python 3.x连接数据库的方式

我们想要在我们的mython程序中使用mysql，首先需要安装pymysql模块，安装方式可以使用cmd命令安装，

pip3.x install pymysql

首先在我们连接数据库之前先创建一个user表，方便测试功能；

CREATE TABLE `user`(
id TINYINT AUTO_INCREMENT KEY,
name CHAR(15) NOT NULL UNIQUE,
password VARCHAR(30) NOT NULL
)ENGINE=INNODB CHARSET=UTF8;

INSERT user (name,password)
VALUES
('moud','123'),
('linda','123'),
('tom','123');

---

---

2 使用Python脚本实现增删改查和事务处理，源码如下：

---

---

# encoding:utf-8
# Author:"richie"
# Date:9/14/2017
import pymysql

conn = pymysql.connect(
    host = 'localhost',
    user= 'root',
    password='123456',
    database='test',
    charset='utf8')

# 获取游标
cursor = conn.cursor()

# 插入数据
sql = """INSERT IGNORE INTO user (name,password) VALUES (%s,%s)"""
data = ('mimi','123')
# cursor.execute(sql,data)
conn.commit()
print('成功插入',cursor.rowcount,'条数据')

# REPLACE works exactly like INSERT

sql = """REPLACE INTO user (name,password) VALUES (%s,%s)"""
data = ('mimi','321')
cursor.execute(sql,data)
conn.commit()
print('成功插入',cursor.rowcount,'条数据')
# 修改数据

sql = """UPDATE user SET password=%s WHERE id=%s"""
data = ('321',1)
cursor.execute(sql,data)
conn.commit()
print('成功修改',cursor.rowcount,'条数据')

# 查询数据
sql = """SELECT name,password FROM user WHERE id = %s"""
data = (2,)
cursor.execute(sql,data)
for row in cursor.fetchall():
print("name:%s\tpasswd:%s" % row)
print('共查找出',cursor.rowcount,'条数据')

# # 删除数据
sql = """DELETE FROM user WHERE id = %s"""
data = (1,)
cursor.execute(sql,data)
conn.commit()
print('成功删除',cursor.rowcount,'条数据')

cursor.close()
conn.close()