一、首先在官网下载对应的python版本和电脑系统
连接:戳这里
对应的python版本 在CMD里面查看,电脑配置是64位的,就在这里下载。
二、下载好之后,在CMD里面安装:输入命令:pip install 和上面下载的包名
三、查找出 oracle 的安装目录下的.dll文件,把所有的.dll文件都复制到我们的python的安装路径下
四、这样就安装完成了,我们可以去pycharm调用代码去试试:
__author__ = "why"
import cx_Oracle as co
#连接数据库,下面括号里内容根据自己实际情况填写
rms_oracle = co.connect('swtonline/swtonline@192.168.2.62:1521/chinapay')
#验证oracle是否连接成功
oracle_version = rms_oracle.version
print (oracle_version)
返回:这样就成功连上我们的数据库了:
定义了一个连接数据库的类,拿走不谢:
import cx_Oracle as Co
class DemoOracle:
def __init__(self):
self.connect = Co.connect('swtonline/swtonline@192.168.2.XX:1521/chinapay') # 连接数据库
self.cursor = self.connect.cursor() # 建游标
def insert(self, sql):
self.cursor.execute(sql)
self.connect.commit()
def delete(self, sql):
self.cursor.execute(sql)
self.connect.commit()
def update(self, sql):
self.cursor.execute(sql)
self.connect.commit()
def select_one(self, sql): # 查找出第一条数据
execute = self.cursor.execute(sql)
return execute.fetchone()
def select_all(self, sql): # 查找出所有数据
execute = self.cursor.execute(sql)
return execute.fetchall()
def close(self): # 关停游标和数据库连接
self.cursor.close()
self.connect.close()
def rollback(self): # 回滚数据
self.connect.rollback()
if __name__ == '__main__':
sql = "DELETE from test11 where USER_NAME='%s'" % ('why',)
DemoOracle().delete(sql)
DemoOracle().close()
对数据库的应用:
from class_oracle.oracle_dome import DemoOracle
from jianlian.timeclass.class_time import DateTime
class T1delete:
@staticmethod
def t1sql(t1, t1_, t2, t2_): # t1昨天的日期、 t1_明天日期 格式(yyyymmdd)、 t2昨天的日期、 t2_明天日期 格式(yyyy-mm-dd)
sql = [
"DELETE FROM TBL_SETT_DATE_DAY WHERE TO_CHAR(DATE_TIME,'yyyyMMdd') between '%s'and '%s' AND SETT_CYCLE = 'T1_day'" % (t1, t1_),
"DELETE FROM TBL_SETT_DATE WHERE TO_CHAR(DATE_TIME,'yyyyMMdd') between '%s'and '%s' AND SETT_CYCLE = '1'" % (t1, t1_),
"DELETE FROM TBL_SETT_DATE_STEP WHERE TO_CHAR(DATE_TIME,'yyyyMMdd') between '%s'and '%s'" % (t1, t1_),
"DELETE FROM TBL_SETT_DATE_STEP_REQ WHERE TRAN_DATE between '%s'and '%s'" % (t1, t1_),
"DELETE FROM TBL_CHARGE_FEE_DETAIL_INFO WHERE IDENTIFY_ID IN ( SELECT DISTINCT IDENTIFY_ID FROM TBL_TRAN_DETAIL_DATA_INFO WHERE SETT_CYCLE = 'T1' AND sett_date between '%s'and '%s' AND MACHINE_TYPE IN ('01', '11') )" % (t1, t1_),
"DELETE FROM TBL_MER_LEVERL_FEE_INFO WHERE IDENTIFY_ID IN ( SELECT DISTINCT IDENTIFY_ID FROM TBL_TRAN_DETAIL_DATA_INFO WHERE SETT_CYCLE = 'T1' AND sett_date between '%s'and '%s' AND MACHINE_TYPE IN ('01', '11'))" % (t1, t1_),
"DELETE FROM TBL_TRAN_DETAIL_DATA_INFO WHERE SETT_CYCLE = 'T1' AND sett_date between '%s'and '%s' AND MACHINE_TYPE IN ('01', '11')" % (t1, t1_),
"DELETE FROM TBL_COLLECT_FEE WHERE ID IN ( SELECT t0.ID FROM TBL_COLLECT_FEE t0 INNER JOIN tbl_reality_amt t1 ON t0.mer_code = t1.mer_code AND t0.create_date = t1.create_date INNER JOIN tbl_SETTLE_ACCOUNT t2 ON t1.mer_code = t2.mer_code AND t1.create_date = t2.sett_date WHERE t1.term_num > 0 AND t2.sett_date between '%s'and '%s')" % (t2, t2_),
"DELETE FROM TBL_REALITY_AMT WHERE REALITY_ID IN ( SELECT t1.REALITY_ID FROM tbl_reality_amt t1 INNER JOIN tbl_SETTLE_ACCOUNT t2 ON t1.mer_code = t2.mer_code AND t1.create_date = t2.sett_date WHERE t2.sett_date between '%s'and '%s')" % (t2, t2_),
"DELETE FROM TBL_CASH_BACK WHERE ID IN ( SELECT t0.ID FROM TBL_CASH_BACK t0 WHERE t0.deductions_date between '%s'and '%s' AND SUBSTR(t0.serial_no, 0, 2) = 'T1')" % (t2, t2_),
"DELETE FROM TBL_DEDUCTIONS_RECORDS WHERE ID IN ( SELECT t0.ID FROM TBL_DEDUCTIONS_RECORDS t0 WHERE SUBSTR(t0.deductions_time, 0, 10) between '%s'and '%s' AND SUBSTR(t0.serial_no, 0, 2) = 'T1')" % (t2, t2_),
"DELETE FROM TBL_SETT_PHONE p WHERE EXISTS ( SELECT t1.* FROM TBL_SETT_PHONE t1 INNER JOIN tbl_SETTLE_ACCOUNT t2 ON t1.mer_code = t2.mer_code AND t1.create_date = REPLACE(t2.sett_date, '-') WHERE t2.sett_date between '%s'and '%s' AND p.mer_code = t1.mer_code AND p.create_date = t1.create_date)" % (t2, t2_),
"DELETE FROM tbl_SETTLE_ACCOUNT WHERE SETT_DATE between '%s'and '%s'" % (t2, t2_),
"DELETE FROM tbl_SETTLE_ACCOUNT_DAY WHERE SETT_DATE between '%s'and '%s'" % (t2, t2_),
"DELETE FROM tbl_SETTLE_ACCOUNT_LIST WHERE SETT_DATE between '%s'and '%s'" % (t2, t2_)]
for i in sql:
try:
DemoOracle().delete(i)
print(i)
except:
DemoOracle().rollback() # 执行失败就 恢复 冲正
print('SQL语句执行错误')
finally:
DemoOracle().close()
if __name__ == '__main__':
t1 = DateTime().random_date(-1) # 取昨天的日期
t1_job = DateTime().random_date(1) # 取明天的日期
t2 = DateTime().random_dates(-1)
t2_job = DateTime().random_dates(1)
T1delete.t1sql(t1, t1_job, t2, t2_job)
更多推荐
python连接数据库oracle
发布评论