python连接数据库
python连接MySQL
# -*- coding:utf-8 -*-
# 原子性(atomicity):一个事务是不可分割的一个整体,执行操作的话,要做就要一起做,不做的话就都不做
# 一致性(consistency):数据库的状态从一个状态变到另外一个状态
# 隔离性(isolation):一个事务在执行的时候不能被其他的事务干扰。即各各事务之间的操作是互不干扰的
# 持久性(durability):一个事务一旦提交以后,对数据库的变化就是永久性的
import pymysql
# 1.链接数据库
# host='127.0.0.1' or 'localhost'
# 端口号没有引号
db = pymysql.Connect(
host='localhost',
user='root',
passwd='cfx123CFX',
db='srs',
port=3306,
charset='utf8'
)
print(db)
# 3. 获取游标 --> 开辟一个缓冲区,用于存放sql语句执行的结果
cursor = db.cursor()
"""
# 4.执行sql 插入数据
try:
sql = '''insert into tbcourse values
(1122,'C22语言程序设计',5,'大神级讲师1授课需要抢座');'''
cursor.execute(sql)
db.commit()
except:
# 5.回滚
db.rollback()
"""
'''
# 4.执行sql 获取数据
sql = "select * from tbstudent;"
cursor.execute(sql)
# 5.获取结构
# fetchone() 获取一条数据
# fetchall() 获取全部数据
# fetchmany(n) 获取n条数据
data = cursor.fetchmany(5)
for i in data:
print('id:%s name:%s' % (i[0], i[1]))
'''
# 4.删除数据
try:
sql = '''delete from tbcourse where cosid = 1112;'''
cursor.execute(sql)
db.commit()
except:
db.rollback()
# 2. 关闭链接
db.close()
python连接redis
1.pip install redis
2.链接mysql
3.链接redis
import redis
redis.Redis()
4.python xxx.py argv1 argv2
import sys
sys.argv[1]
5.访问redis,判断输入的姓名和密码和redis中保存的用户名和密码是否匹配
6.redis不匹配,则查询mysql,select 操作
7.mysql 有查询结果的话,则更新到redis中,反之没有该用户
# -*- coding:utf-8 -*-
import sys
import redis
import pymysql
def con_mysql(sql):
db = pymysql.connect(
host='127.0.0.1',
user='root',
passwd='cfx123CFX',
port=3306,
db='company',
charset='utf8'
)
cursor = db.cursor()
cursor.execute(sql)
data = cursor.fetchall()
db.close()
return data
def con_redis():
r = redis.Redis(host='47.106.123.33',port=6379,password='cfx123CFX')
return r
def checke_redis(name,passwd):
r_name = con_redis().hget('user', 'name').decode('utf-8')
r_passwd = con_redis().hget('user', 'passwd').decode('utf-8')
return (True,'登陆成功') if name == r_name and passwd == r_passwd else (False,'登陆失败')
def add_redis(name,passwd):
con_redis().hset('user','name',name)
con_redis().hset('user','passwd',passwd)
if __name__ == '__main__':
if len(sys.argv) == 3:
name = sys.argv[1]
passwd = sys.argv[2]
result = checke_redis(name,passwd)
if not result[0]:
sql = '''select * from user where name="%s" and passwd="%s"''' % (name,passwd)
data = con_mysql(sql)
if data:
add_redis(name,passwd)
print('刷新redis,登陆成功')
else:
print('用户名或密码错误')
else:
print('redis中存在用户信息,登陆成功')
else:
print('传入的参数不正确')
数据库连接中生产者与消费者思想
模拟推送消息给客户的情形:客户端会一直挂机等待接收消息,而推送端运行一次推送一次消息
主文件
# -*- coding:utf-8 -*-
import redis
class RedisBase(object):
def __init__(self):
self.__conn = redis.Redis(
host='47.106.123.33',
port=6379,
password='cfx123CFX')
self.pub = 'test'
self.sub = 'test'
# 发布
def publish_msg(self,msg):
self.__conn.publish(self.pub,msg)
# 订阅
def subscribe_msg(self):
pub = self.__conn.pubsub()
pub.subscribe(self.pub)
pub.parse_response()
return pub
推送端
# -*- coding:utf-8 -*-
from redisChannel import RedisBase
obj = RedisBase()
msg = 'hello world'
obj.publish_msg(msg)
接收端
# -*- coding:utf-8 -*-
from redisChannel import RedisBase
obj = RedisBase()
redis_sub = obj.subscribe_msg()
while True:
msg = redis_sub.parse_response()
print('msg:%s' % msg)