连接数据库
创建表
# _*_coding:utf-8_*_
# 作者 :渝周郎
# 创建时间：2019/6/1821:00
# 文件 ：01连接数据库.py
# IDE ：PyCharm
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 创建表
sql = """
create table pymysql_users(
id INT auto_increment primary key ,
name char(25) NOT NULL UNIQUE,
age tinyint,
sex char(10) not null
)engine=innodb default charset=utf8;
"""
cursor.execute(sql)
cursor.close()
conn.close()
字典返回的游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
注意： charset="utf8",千万不要写成"utf-8"
增
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 增
sql = "insert into pymysql_users(name, age, sex) values (%s, %s, %s);"
name = "小王"
age = 19
sex = "男"
ret = cursor.execute(sql, [name, age, sex])
conn.commit()
print(ret)
cursor.close()
conn.close()
插入数据失败回滚
在执行增删改操作时，如果不想提交前面的操作，可以使用 rollback() 回滚取消操作。
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 增
sql = "insert into pymysql_users(name, age, sex) values (%s, %s, %s);"
name = "小王"
age = 19
sex = "男"
try:
# 执行SQL语句
cursor.execute(sql, [name, age, sex])
# 提交事务
conn.commit()
except Exception as e:
print(e)
# 有异常，回滚事务
conn.rollback()
cursor.close()
conn.close()
获取插入数据的ID(cursor.lastrowid)
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 增
sql = "insert into pymysql_users(name, age, sex) values (%s, %s, %s);"
name = "小王"
age = 19
sex = "男"
try:
# 执行SQL语句
cursor.execute(sql, [name, age, sex])
# 提交事务
conn.commit()
# 提交之后，获取刚插入的数据的ID
last_id = cursor.lastrowid
print(last_id)
except Exception as e:
print(e)
# 有异常，回滚事务
conn.rollback()
cursor.close()
conn.close()
批量执行(executemany)
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 增
sql = "insert into pymysql_users(name, age, sex) values (%s, %s, %s);"
# 批量数据
data = [("小周", 23, "女"), ("小赵", 34, "女"), ("老钱", 65, "男")]
try:
# 执行SQL语句
cursor.executemany(sql, data)
# 提交事务
conn.commit()
except Exception as e:
print(e)
# 有异常，回滚事务
conn.rollback()
cursor.close()
conn.close()
删
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 删
sql = "delete from pymysql_users where id=%s;"
try:
# execute第二参数只能是元组、列表、字典
cursor.execute(sql, [3])
conn.commit()
except Exception as e:
conn.rollback()
cursor.close()
conn.close()
改
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 改
sql = "update pymysql_users set age=%s where name=%s;"
try:
cursor.execute(sql, [25, "小周"])
conn.commit()
except Exception as e:
conn.rollback()
cursor.close()
conn.close()
查
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
user="数据库用户名",
password="数据库密码",
database="数据库名称",
charset="utf8"
)
cursor = conn.cursor()
# 单条数据查询
sql1 = "select * from pymysql_users where name=%s;"
# 执行SQL语句
cursor.execute(sql1, ("小周",))
# 获取单条查询数据
ret = cursor.fetchone()
print(ret)
# 多条数据查询
sql2 = "select * from pymysql_users"
cursor.execute(sql2)
ret2 = cursor.fetchall()
print(ret2)
# 指定条数查询
cursor.execute(sql2)
ret3 = cursor.fetchmany(2)
print(ret3)
# 最后关闭游标和连接
cursor.close()
conn.close()
游标移动
# 光标按绝对位置移动1
cursor.scroll(1, mode="absolute")
# 光标按照相对位置(当前位置)移动1
cursor.scroll(1, mode="relative")
