Mysql
#以java来理解python连接mysql
#JDBC = DB-API规范
#驱动 = python对应数据库的模块
#流程
'''
引入API模块
获取连接
执行语句和存储过程
关闭数据库连接
'''
import pymysql
#一些参数
pymysql.apilevel
pymysql.__file__
pymysql.threadsafety #线程安全等级
pymysql.paramstyle='qmark' #默认pyformat
#获取连接
db=pymysql.connect('localhost','root','a')
#python中需要通过游标来进行操作
cursor=db.cursor()
#新建一张表
cursor.execute('DROP TABLE IF EXISTS EMPLOYEE')
'''sql语句'''
sql="""create table EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT)"""
cursor.execute(sql)
db.close()
增删改查
#增
db=pymysql.connect('localhost','root','a','user')
cursor=db.cursor()
sql="""INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME,AGE,SEX,INCOME)
VALUES ('Mac','Mohan',20,'M',2000)"""
try:
cursor.execute(sql)
#提交到数据库执行
db.commit()
print('添加成功')
except:
db.rollback()
db.close()
#或者使用占位符添加数据
pymysql.paramstyle='format'
db=pymysql.connect('localhost','root','a','user')
cursor=db.cursor()
sql="""INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME,AGE,SEX,INCOME)
values (%s,%s,%s,%s,%s)"""
try:
cursor.execute(sql,('Macc','Mohan','27','M','2000'))
#提交到数据库执行
db.commit()
print('添加成功')
except Exception as e:
print(e)
db.rollback()
db.close()
删
pymysql.paramstyle='format'
db=pymysql.connect('localhost','root','a','user')
cursor=db.cursor()
sql="delete from employee where age<%s"
try:
cursor.execute(sql,'25')
db.commit()
except Exception as e:
print(e)
db.close()
改
#更新数据
pymysql.paramstyle='format'
db=pymysql.connect('localhost','root','a','user')
cursor=db.cursor()
sql="update employee set age=age+1 where sex='%c'" % ('M')
try:
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
db.close()
查
#查所有数据
pymysql.paramstyle='format'
db=pymysql.connect('localhost','root','a','user')
cursor=db.cursor()
sql="select * from employee where income>'%d'" % (1000)
try:
cursor.execute(sql)
results=cursor.fetchall()
for row in results:
fname=row[0]
lname=row[1]
age=row[2]
sex=row[3]
income=row[4]
print((fname,lname,age,sex,income))
except Exception as e:
print(e)
db.close()
Mongo(集群环境)
import pymongo
#连接mongo
client=pymongo.MongoClient('mongodb://192.168.146.200:23000')#集群
db=client.test#指定数据库
#db=client('test')
collection=db.students
#collection=db('students')
#增
#插入一条数据
student={
'id':'1',
'name':'zs',
'age':20,
'gender':'male'
}
result=collection.insert_one(student)
#插入多条数据
student2={
'id':'2',
'name':'ls',
'age':21,
'gender':'male'
}
student3={
'id':'3',
'name':'ww',
'age':25,
'gender':'male'
}
result=collection.insert_many( (student2,student3) ) #可以输出id编号验证
print(result.inserted_ids)
在mongo中验证
#查询
#查询一条
result=collection.find_one({'name':'zs'})
print(type(result))
print(result)
#查询多条
result=collection.find({'age':20})
print(type(result))
print(result)
for r in result:
print(r)
#如果想要根据mongo自动生成的object id查询
#需要在添加数据时将id保存下来而不能直接用字符串作为find的参数进行查询
#更多条件查询
'''
$lt < $gt > $lte <= $gte >= $ne != $in $nin
'''
results=collection.find({'age':{'$gte':20}})
for result in results:
print(result)
#正则表达式
results=collection.find({'name':{'$regex':'^w.*'}}) #^字符串开头 即以w开头 .*后面跟任意字符
for result in results:
print(result)
#排序
results=collection.find().sort('name',pymongo.ASCENDING) #升序排列
print( [result['name'] for result in results])
#偏移 --用于分页
results=collection.find().sort('name',pymongo.ASCENDING).skip(1).limit(2)
print( [result for result in results])
#更新
condition={'name':'zs','age':20}
student1={}
student1['age']=25
result=collection.update_many(condition,{'$set':student1})
print(result.matched_count,' ',result.modified_count)
#删除多条
result=collection.delete_many( {'age':{'$lte':24} } )
print(result.deleted_count)