2019年12月10日
image.png
mysql -uroot -pwy123456
* 创建数据库 */
CREATE DATABASE IF NOT EXISTS MyDB;
use MyDB;
/* 用户表 */
CREATE TABLE IF NOT EXISTS user (
name varchar(20), /*name */
userid int, /* useid */
PRIMARY KEY (userid));
/* 插入数据 */
INSERT INTO user VALUES('Tom',1);
INSERT INTO user VALUES('Ben',2);
一.查询：
1.有条件查询
import pymysql
# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='MyDB',
charset='utf8')
try:
# 2\. 创建游标对象
with connection.cursor() as cursor:
# 3\. 执行SQL操作
# 3.1 通过元组绑定
# sql = 'select name,userid from user where userid > %s'
# cursor.execute(sql, [0])
# 3.2 通过字典绑定
sql = 'select name,userid from user where userid > %(id)s'
cursor.execute(sql, {'id': 0})
# 4\. 提取结果集
result_set = cursor.fetchall()
for row in result_set:
print('id:{0} - name:{1}'.format(row[1], row[0]))
# cursor.close()
# with代码块结束 5\. 关闭游标
finally:
# 6\. 关闭数据连接
connection.close()
image.png
2.无条件查询
# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='MyDB',
charset='utf8')
try:
# 2\. 创建游标对象
with connection.cursor() as cursor:
# 3\. 执行SQL操作
# 3.1 通过元组绑定
# sql = 'select name,userid from user where userid > %s'
# cursor.execute(sql, [0])
# 3.2 通过字典绑定
sql = 'select max(userid) from user'
cursor.execute(sql)
# 4\. 提取结果集
row = cursor.fetchone()
if row is not None:
print('最大用户id:{0}'.format(row[0]))
# cursor.close()
# with代码块结束 5\. 关闭游标
finally:
# 6\. 关闭数据连接
connection.close()
image.png
二.修改操作
1.增
import pymysql
# 查询最大用户Id
def read_max_userid():
# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='MyDB',
charset='utf8')
try:
# 2\. 创建游标对象
with connection.cursor() as cursor:
# 3\. 执行SQL操作
sql = 'select max(userid) from user'
cursor.execute(sql)
# 4\. 提取结果集
row = cursor.fetchone()
if row is not None:
print('最大用户Id ：{0}'.format(row[0]))
return row[0]
# with代码块结束 5\. 关闭游标
finally:
# 6\. 关闭数据连接
connection.close()
# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='MyDB',
charset='utf8')
# 查询最大值
maxid = read_max_userid()
try:
# 2\. 创建游标对象
with connection.cursor() as cursor:
# 3\. 执行SQL操作
sql = 'insert into user (userid, name) values (%s,%s)'
nextid = maxid + 1
name = 'Tony' + str(nextid)
affectedcount = cursor.execute(sql, (nextid, name))
print('影响的数据行数：{0}'.format(affectedcount))
# 4\. 提交数据库事物
connection.commit()
# with代码块结束 5\. 关闭游标
except pymysql.DatabaseError:
# 4\. 回滚数据库事物
connection.rollback()
finally:
# 6\. 关闭数据连接
connection.close()
image.png
2.改
import pymysql
# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='MyDB',
charset='utf8')
try:
# 2\. 创建游标对象
with connection.cursor() as cursor:
# 3\. 执行SQL操作
sql = 'update user set name = %s where userid > %s'
affectedcount = cursor.execute(sql, ('Tom', 2))
print('影响的数据行数：{0}'.format(affectedcount))
# 4\. 提交数据库事物
connection.commit()
# with代码块结束 5\. 关闭游标
except pymysql.DatabaseError as e:
# 4\. 回滚数据库事物
connection.rollback()
print(e)
finally:
# 6\. 关闭数据连接
connection.close()
image.png
3删
import pymysql
# 查询最大用户Id
def read_max_userid():
# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='MyDB',
charset='utf8')
try:
# 2\. 创建游标对象
with connection.cursor() as cursor:
# 3\. 执行SQL操作
sql = 'select max(userid) from user'
cursor.execute(sql)
# 4\. 提取结果集
row = cursor.fetchone()
if row is not None:
print('最大用户Id ：{0}'.format(row[0]))
return row[0]
# with代码块结束 5\. 关闭游标
finally:
# 6\. 关闭数据连接
connection.close()
# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='MyDB',
charset='utf8')
# 查询最大值
maxid = read_max_userid()
try:
# 2\. 创建游标对象
with connection.cursor() as cursor:
# 3\. 执行SQL操作
sql = 'delete from user where userid = %s'
affectedcount = cursor.execute(sql, (maxid))
print('影响的数据行数：{0}'.format(affectedcount))
# 4\. 提交数据库事物
connection.commit()
# with代码块结束 5\. 关闭游标
except pymysql.DatabaseError:
# 4\. 回滚数据库事物
connection.rollback()
finally:
# 6\. 关闭数据连接
connection.close()
image.png
三.其他NoSQL数据库存储
image.png
image.png
import dbm
with dbm.open('mydb', 'c') as db:
db['name'] = 'tony' # 更新数据
print(db['name'].decode()) # 取出数据
age = int(db.get('age', b'18').decode()) # 取出数据
print(age)
if 'age' in db: # 判断是否存在age数据
db['age'] = '20' # 或者 b'20'
del db['name'] # 删除name数据
image.png
如果您发现本文对你有所帮助，如果您认为其他人也可能受益，请把它分享出去。
网友评论