Python TIPS
前言
记忆力不行了, 每次都上网查资料。把常用的方法集中记录在这儿吧!
一、零散Tips
- 显示当前时间:
print(f"{time.strftime('%H:%M:%S', time.localtime())}==正在查询第{i} - {i + 1000}条数据=========")
- 读取excel和csv至dataframe:
# excel
xlsx = pd.ExcelFile(file)
df = pd.read_excel(xlsx, "Sheet1", dtype=str)
# csv
df = pd.read_csv(path + file, dtype=str)
- 正则表达式匹配:
import re
pattern = re.compile(r'\d+') # 查找数字
re1 = pattern.findall('7760777短号,81064107长号 7765678短号,81065574长号 谢谢')
re2 = pattern.search('runoob 123 google 456')
# 123 只匹配了一次,匹配首个符合要求的字符串
print(re1) # 匹配结果列表
print(re2) #匹配结果字符串
二、Python操作数据库的方法
1.常规方式
代码如下(mysql):
import pymysql
db = pymysql.connect(host='x.x.x.x', user='xxx', passwd='xxx', db='xxx', port=xxx, charset='utf8')
try:
# 检验数据库是否连接成功
cursor = db.cursor()
records = cursor.execute(f"SELECT * FROM `{table}` order by {pk} limit {offset},1000")
if not records:
print("Not get records!")
return []
print(f"共查询到 {records} 条记录")
all = cursor.fetchall()
for x in all:
data.append(
dict(
zip(
[k[0] for k in cursor.description],
[v for v in x]
)
)
)
except pymysql.Error as e:
print(e)
print('操作数据库失败')
finally:
# 如果连接成功就要关闭数据库
if db:
db.close()
Oracle代码:
import cx_Oracle
tns = cx_Oracle.makedsn('x.x.x.x', port, 'schem')
db = cx_Oracle.connect('user','password', tns)
cr = db.cursor()
sql = "select * from table where rownum<3"
cr.execute(sql)
for field in cr.description:
print(f"{field[1]} - {field[0]}")
#print(f'[Column("{field[0]}")]')
rs = cr.fetchall()
for x in rs:
print(x)
#print(dict(zip([k[0] for k in cr.description], [v for v in x])))
cr.close()
db.close()
2.使用ORM框架Sqlalchemy
代码如下(示例):
import pandas as pd
from sqlalchemy import create_engine
import sqlalchemy
ORACLE_ENGINE = create_engine(f'oracle+cx_oracle://{oc.User}:{oc.Pass}@{oc.IP}:{oc.Port}/{oc.Instance}')
MYSQL_ENGINE = create_engine('mysql+pymysql://root:password@localhost/dbname?charset=utf8')
# 1. 从数据库读入至dataframe
def get_datas_from_yzb():
sql = "select * from table"
return pd.read_sql(sql, ORACLE_ENGINE)
# 2. dataframe数据入库, 数据库表并不会建立索引, 影响查询速度, 可用原生sql建立索引
df.to_sql('match_of_client_and_service', MYSQL_ENGINE, index=False, if_exists='replace')
# 为Table增加主键, 以更快地查询
try:
sql = "alter table table add primary key(业务账号_x(50))"
MYSQL_ENGINE.execute(sql)
except Exception as e:
print(e)