python学习笔记第9章：数据库操作
9.1Mongodb
1.1.1 连接
from pymongo import MongoClient
def conMon(collection):
client = MongoClient(
'mongodb://用户名:密码@ip:port,1.20.1.7:27017,1.16.2.2:27017/admin')
db = client.数据库名称
c = db.get_collection(collection)
return c
#admin审计数据库
c = conMon(‘表名’)
命令行
mongo --host 127.0.0.1 --port 27017 -u username -p passwd
1.1.2 查询
data = find_one({})
data = find({})
模糊搜索
"c1" : {"$not" : re.compile(r'中|层')} #r非必须
"c1" : {'$regex':'邮件'} #$regex : 模糊的字
统计行数
- count_documents({"region": region})
data.count()
去重
db.getCollection("t1").distinct("日期",
{
"detail" : "今天"
}
)
排序(获取最大值或最小值)
next(coll.find({}, {"date":1}).sort([("date", -1)]).limit(1))
倒序获取最大值, 只要第一条记录
查询某个字段重复的项
db.t1.aggregate([
{ "$group": { "_id" : '$name',
"count": { "$sum" : 1 } } },
{ "$match": { "count": { "$gt" : 1} } }
])
#查询name重复的项
查询非禁用项的重复值, match可多次出现
db.t1.aggregate([
{ "$match": { "disabled": { "$exists" : false} } },
{ "$group": { "_id" : '$name',
"count": { "$sum" : 1 } } },
{ "$match": { "count": { "$gt" : 1} } }
])
查看索引
db.c.getIndexes()
聚合查询
data = t1.aggregate([
{ "$group": {
'_id' : '$scene_id', #1.以scene_id分组
'zx' : {"$sum": "$资管.values.专线"}, #2.
'month' : {"$sum": 1} #3.文档数汇总
}}
])
for i in data:
print(i["_id"], i["zx"], i["month"])
- 名字必须是_id, 用scene_id分组
- 名字随便起, 对资管.values.专线求和, 即sum
- 名字随便起, 统计一共有多少条数据, 即count
设置超时时间
data = c.find({},{},no_cursor_timeout = True)
data.close()
设置为永不超时，在执行完毕手动关闭
1.1.3 修改
修改/新增
插入
新增
c.insert_one({'数据日期': c1}) #插入一条数据，
c.insert_one(doc)
c.insert_many(doc)
更新，默认不存在字段时插入字段
c.update_one({'数据日期': c1}, #查询条件
{'$set':{‘ab.cd’:3}}, #要更新的字段
upsert=True) #缺省False,为True查询不到会插入文档
c.update_one({'手机号':bill},
{'$set':
{'update_time':update_time},
'$push':
{'major_info':
{'major':major,
'bill':bill }}},
upsert=True)
c.update_one({'手机号':bill},
{'$push':
{'major_info': “套餐1”
}},
upsert=True)
#更新多个
data = c.update_many({条件}，{要更新的内容})
data c.update_many({}，{要更新的内容}) #更新全部
data.modify_count() #修改的行数, 大概是这样写的吧
修改字段名
- update_many({"update_time":{"$gte" : ("2021")}},
{"$rename":
{"合计" : "占比" }})
#原字段名：新字段名
可以修改全部数据的字段名, 也可以只修改部分文档的字段名
字段自增
{"$set":{ },
"$inc":{"version":1} #和set在一个花括号内
}
添加索引
- ensureIndex({"编号":1},{background:true})#单字段索引
db.c.ensureIndex({"nam":1},{"unique":true, background:true}) #唯一索引
c.ensureIndex({"date":1,"update_time":1},{background:true}) #复合索引
reIndex() 重建索引
删除索引dropIndex(“索引名”)
删除全部索引dropIndexes()
,{background:true} 选项防止锁表
from pymongo import ASCENDING
c.create_index([(‘u_id’,ASCENDING)]) #在u_id列创建自增长索引
c.hint([(‘_id’,ASCENDING)]) #指定使用_id为索引
数组操作
cc.update_one({"phone":phone},
{"$push":{"fg": depart}})
已存在不添加
cc.update_one({"phone":phone},
{"$addToSet":{"fg": depart}})
批量更新
from pymongo import UpdateOne
col = []
n = 0
col.append(UpdateOne({"phone": phone},
{"$set": {
"_current_tags.is": "幸福家"
}, “$push”:{}
}, upsert=True))
n +=1
if n == 15000:
t1.bulk_write(col, ordered=False)
col = []
n = 0
user.bulk_write(col, ordered=False)
#ordered=False无序执行, 前面的失败不影响后面的
批量插入
方法一:
from pymongo import InsertOne
col.append(InsertOne({“key”:”value”}))
user.bulk_write(col)
方法二:
col.append({“key”:”字典型”}) #追加字典
t1.insert_many(col)
1.1.4 删除
删除文档
c.delete_one({'id_act':row}) #删除一条
c.delete_many({}) #删除全部
c.deleteMany({"type_detail" : "老数据",
"c1" : NumberInt(0)});
删除字段
c.update_one({'数据日期': c1}, #查询条件
{'$unset':{ 'disabled':'' }}, #要删除的字段
c.update_many() #删除查询到的多条数据
删除数组中符合条件的项
c.update_one({"user_id" : “id”}, #查询条件
{"$pull":{"rights":{"role":"test",
"depart":"江"}}})
#在数组rights中，删除role为test，depart为江的项
批量删除
from pymongo import DeleteMany, DeleteOne,
from pymongo.errors import BulkWriteError #异常处理
col.append(DeleteOne({'i': 5}))
col.append(DeleteMany({'i': 5}))
try:
user.bulk_write(col)
except BulkWriteError as bwe:
pprint(bwe.details)
1.1.5 其他
null
python中用None代替
NaN 为 在mongo为Double类型 “name”:NaN
在python用np.nan
判断游标结果集是否为空，适用各种数据库
if len(list(data)) == 0 #等于0为空
ISODATE
ISODate("2021-09-25T08:50:01.472+0800")
替换为
import dateutil
dateutil.parser.parse("2021-09-25T08:50:01.472+0800")
#括号内的部分都不用变
1.1 Oracle
1.1.1 连接
导入模块，安装oracle客户端
import cx_Oracle
#cx_Oracle依赖oracle客户端，环境变量找不到客户端时，用以下语句指定
#cx_Oracle.init_oracle_client(lib_dir=r"C:\Program Files\instantclient_19_10")
创建连接
def conOra(user='',password='' , ip_port_instan='8.8.8.8:1521/rhcl' , encoding = 'UTF-8',nencoding ='UTF-8'):
connection = cx_Oracle.connect(user,password, ip_port_instan , encoding='UTF-8', nencoding='UTF-8')
return connection.cursor()
#return connection #如需使用提交回滚等操作，不能直接返回游标
使用连接
cursor = conOra()
#db = conOra() #如需使用提交回滚等操作，不能直接返回游标
#cursor = db.cursor()
执行命令(单引号，双引号，三引号均可）
cursor.execute(''select name from t1'')
cursor.execute( f''select {name} from level_zone'')
执行命令方式二
sql = ''select ZONE_NAME from t1''
sql = ""select activity_id from t1""
cursor.execute(sql)
提交回滚
db.rollback()
db.commit()
1.1.2 添加insert into t1
insert into t1 (字段) values(值） #字符串值要用引号，字段不用引号
insert into tab1 select * from tab2 #一个表插入另一个表
Merge into
merge into yytbt t1
using yytb_tmp t2
on (t1.编号=t2.编号)
when matched then
update set t1.a=t2.a
when not matched then
insert (c1,c2,c3)
values (t2.a,t2.b,t2.c) '''
存在更新，不存在插入，可以把数据插入临时表t2进行更新
sql = ''' merge into yytbt_ivr t1
using yytbt_ivr_tmp t2
on (t1.编号=t2.编号)
when matched then
update set t1.营 = t2.营,t1.编号 = t2.编号
where t1.成功量<>t2.成功量 or t1.状态<>t2.状态
when not matched then
insert (c1,c2)
values(t2.编号,t2.营业) '''
sql2 = '''merge into scene t1 using scene_tmp t2 on (t1.场景 = t2.场景)
when matched then
update set t1.c1 = t2.c1,t1.c2 = t2.c2 where 1=1
when not matched then
insert (c1,c2,c3) VALUES(t2.c2,t2.c2) '''
注：on连接的字段出现在update语句可能会出错，不带where条件也可能出错
插入空值
‘’ #空的双引号
插入日期
直接datetime.now() ,不用to_date
或 sysdate #有时会出错
生成插入语句,使用绑定变量
def ora(type, table='t'): #1字符串, 2列表
if type == 1:
col = 'c1, c2, c3,type'
col = col.split(',')
col = [x.strip() for x in col]
elif type == 2:
col = [ c1, c2, c3 ]
print(col, end='\n\n')
print(
f"sql='''insert into {table}" +
"""_tmp({}) values({}) '''.format(','.join(col), ', '.join([':' + str(x) for x in range(0, len(col))])) """
)
#要复制过去的
col_data=[]
n = 0
cursor.setinputsizes(None, 20) #设置字段最大值20个字符
for i in data:
col_data.append((i[0], i[1]))
n +=1
if n == 20000:
cursor.executemany(sql, col_data)
n = 0
col_data=[]
if col_data:
cursor.executemany(sql, col_data)
col_m1 = []
for i in col:
col_m1.append('t1.' + i + '=t2.' + i)
col_m2 = []
for i in col:
col_m2.append('t2.' + i)
###################
print("\nsql = \'\'\'")
m1 = f''' merge into {table} t1
using {table}_tmp t2
'''
m2 = """
on (t1. = t2.)
when matched then
update set {}
when not matched then
insert ({}) VALUES({})'''.format(', '.join(col_m1), ','.join(col), ', '.join(col_m2)) """
print(m1, m2)
1.1.3 删除delete from t1 where num = 7
sql = f''' delete from t1 where 申请时间 <
to_date('{(time - datetime.timedelta(180)).strftime('%Y-%m-%d%H:%M:%S')}', 'YYYY-MM-DD HH24:MI:SS') '''
#删除指定日期之前的数据
#删除user_id重复的数据
delete from t1 where rowid not in (select max(rowId) from t1 group by user_Id)
删除主键(不用写字段，主键就一个）
ALTER TABLE t1 DROP PRIMARY KEY ;
删除列
ALTER TABLE t1 DROP COLUMN c1
删除指定字符(删除c1字段中的楼和层, &可以是任意c1中不存在的字符)
select translate(c1 ,'&楼层','&') from t1
1.1.4 修改update t1 set
update t1 set c1= v1 where
update t1 set (字段1, c2) = (值1, v2)
#一列更新另一列
update t1 set name= substr(覆盖区域名称, instr(覆盖区域名称, '-')+1)
1.1.5 查询select
数据分列(以-符号对拆分c1列, -之前c2, 之后c3)
select c1,
substr(c1, instr(c1, '-')-1) as c2
substr(c1, instr(c1, '-')+1) as c3
from t1 where rownum<10
#查询当前系统时间
select sysdate from dual
#时间转换
to_date(update_time,'YYYY-MM-DD HH24:MI:SS')
#查询非重复项
SELECT * FROM t1 WHERE rowid IN (SELECT max(rowid) FROM t1 WHERE lng IS NOT NULL group by c1 )
#查询表头
col = [ d[0] for d in cursor.description ]
#获取第一条查询结果,
next(cursor.execute(sql))[0]
结果为元组类型, 需要用索引获取
#查询第100行以后的数据
select c1 ,c2 from ( select rownum n, c1 ,c2 from t1 ) where n>100
注:内层rownum 一定要用别名, 不然会冲突出错
#正则表达式
1.匹配出c1列开头的数字
select regexp_substr(c1 , '^\d+') from t1
#删除特定字符
select translate(c1 ,'1楼层','1') from t1
1.1.6 alter
添加字段
alter table t1 add c1 字段类型;
添加主键
alter table t1 add primary key (字段名);
删除
删除user_id重复的数据
delete from t1 where rowid not in (select max(rowId) from t1 group by user_Id)
删除主键(不用写字段，主键就一个）
ALTER TABLE t1 DROP PRIMARY KEY ;
删除列
ALTER TABLE t1 DROP COLUMN c1
修改
修改字段名
alter table t1 rename column c1 to c2;
改表名
alter table t1 rename to t2
改字段类型
alter table t1 modify c1 varchar2(22)
改字段长度
ALTER TABLE t1 MODIFY c1 varchar2(500)
改字段可以为空
alter table t1 modify c1 null
1.1.7 系统信息查询
#连接当前系统连接数
select count(*) from v$process
#查询最大连接数设置
select value from v$parameter where name = 'processes'
1.1.8 表操作
获取表的全部字段
select wm_concat(column_name) from user_tab_columns where table_name=upper('tableName')
获取某个表的建表语句(可以看字段类型)
select dbms_metadata.get_ddl('TABLE',upper('表名')) from dual;
#查询表字段类型
select * from all_tab_cols where table_name=’大写表名’
清空表，无法rollback
truncate table t1
添加索引
CREATE INDEX i1 ON t1(c1)
#
grant select on zgroup_phone to mirror
1.2 MySQL
1.2.1 连接
import pymysql
def conMysql():
db= pymysql.connect('ip:port','user','pass','数据库名称')
return db.cursor() #端口为默认3306可省略
cursor = conMysql()
sql = ''' '''
cursor.execute(sql)
cursor.commit() #游标可直接提交
1.2.2 查询
cursor.execute(sql)
for i in cursor.fetchall():
浙公网安备 33010602011771号