python3连接mysql与mongo

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)