利用python在Oracle数据库中生成密码字典库

思路:

1.生成全字符的单密码有94个,两个密码有94*94,三个密码有94*94*94个

2.连接oracle 需要日志文件

3.日志文件要有详细描述用logging

3.连接到oracle数据库,将生成的密码字典插入oracle表

3 验证字典是否成功插入

第一步

def threefor():
    data=[]
    for key1 in word:
        for key2 in word:
            for key3 in word:
                data.append({'wordkey': key1 + key2 + key3})
    return data

第二步连接到数据库:这个是db_config_dba.py

import cx_Oracle
username=""
password=""
host=""
port=
instance=""
tns = cx_Oracle.makedsn(host,port,instance)
con=cx_Oracle.connect(username,password,tns, mode=cx_Oracle.SYSDBA, encoding="UTF-8" ,nencoding="UTF-8")

第三步日志文件 newfile.py

import os,time

def newfile(targetfile):
    path = os.path.dirname(os.getcwd())
    logpath = os.path.join(path, "worklog")
    logfileformat = str(targetfile+time.strftime("_%Y-%m-%d_%H_%M_%S", time.localtime()) + '.log')
    createlogfile = os.path.join(logpath, logfileformat)
    createlogfile = createlogfile.replace('\\', '/')
    with open(createlogfile, mode="a", encoding="utf-8") as f:
        pass
    print(f"this logfile : {createlogfile} is create by programer :{targetfile}")
    return createlogfile

第四步: 开始logging logconf.py

import logging
import sys
from conf import newfile
def logconf(logtarget):
    logger = logging.getLogger('main')
    logger.setLevel(logging.INFO)
    formatter = logging.Formatter(fmt='%(asctime)s - %(name)s - %(filename)s - %(funcName)s - %(levelname)s - %(message)s',datefmt="%d-%m-%Y %H:%M:%S")
    # # standoutput
    stream_handler = logging.StreamHandler(sys.stdout)
    stream_handler.setLevel(level=logging.INFO)
    stream_handler.setFormatter(formatter)
    logger.addHandler(stream_handler)

    logfile=newfile.newfile(logtarget)
    file_handler = logging.FileHandler(logfile)
    file_handler.setLevel(level=logging.INFO)           # 设置日志级别
    file_handler.setFormatter(formatter)                # 添加日志格式
    logger.addHandler(file_handler)                     # 添加处理器
    return logger

第五步,执行:

import datetime
import gc
import os
from conf import db_config_dba,logconf


def timecost(func):
    def inner(*args, **kwargs):
        starttime = datetime.datetime.now()
        func_name = str(func).split(' ')[1]
        # logger.info('Start doing %s ', func_name)
        logger.info('Start doing %s and detail parameter: %s ', func_name,(args or kwargs or ('No parameter')))
        result = func(*args, **kwargs)
        # func_name = str(func).split(' ')[1]
        # logger.info('%s done', func_name)
        logger.info('Execute {} using times: {}s'.format(func_name, datetime.datetime.now() - starttime))
        logger.info("%s is done successfully with detail parameter: %s '\n'", func_name,(args or kwargs or ('No parameter')))

        return result
    return inner

@timecost
def threefor(name='get three length password library'):
    three=[]
    for key1 in word:
        for key2 in word:
            for key3 in word:
                three.append({'wordkey': key1 + key2 + key3})
    return three

@timecost
def getword(name='get password character sets'):
    lowercase = 'abcdefghijklmnopqrstuvwxyz'
    uppercase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    digits = '0123456789'
    special = """!"#$%&'( )*+,-./:;<=>?@[]^_`{|}~"""
    word = lowercase + uppercase + digits + special
    logger.info('word content is: %s and total with %d', word,len(word))
    return word

@timecost
def executesql(sql):
    try:
        cursor.execute(sql)
    except Exception:
        logger.error('Faild to execute %s', sql,exc_info=True)
    else:
        # logger.info("Execute %s successfully", sql)
        pass

@timecost
def executemanysql(name='executemany'):
    try:
        logger.info('Start batch execute %s',sql)
        cursor.executemany(sql,threefor())
    except Exception:
        logger.error('Faild to  batch execute %s', sql,exc_info=True)
    else:
       pass

@timecost
def sqlcommit(name='commit sql in this module'):
    try:
        db.commit()
    except Exception:
        logger.error("Faild to commit: %s", sql, exc_info=True)
    else:
        pass

@timecost
def condb(name="connect to oracle database"):       #create oracle db connection
    try:
        db = db_config_dba.con
    except Exception:
        logger.error('Faild to gconnection to oracle Database', exc_info=True)
    else:
        cursor = db.cursor()
        return db,cursor

@timecost
def closedb():
    try:
        cursor.close()
        db.close()
    except Exception :
        logger.error('Faild to gconnection to oracle Database', exc_info=True)

if __name__ == "__main__":

    gc.collect()
    logtarget = os.path.basename(__file__).split('.')[0]
    logger = logconf.logconf(logtarget)        #init log config
    word = getword()                  #get password character set
    db,cursor=condb()                         #connect to oracle db

    sql = 'drop table librarykey purge'
    executesql(sql)
    sql = 'create table librarykey ( wordkey varchar(20),CONSTRAINT work_key primary key (wordkey))'
    executesql(sql)
    sql = 'insert into librarykey (wordkey) values (:wordkey)'
    executemanysql(sql)

    # verfication the key number's as the sql result is tuple, so we should use row[0]
    sql = 'select count(*) from  librarykey'
    executesql(sql)
    for threekey in cursor:
        threekey[0]
    if threekey[0] == len(word)*len(word)*len(word):
        logger.info('total password number should be %d:',threekey[0])
        sqlcommit()
    else:
        print("not all the password is generate,pls try again")
        logger.error('password library set in oracle db is failed', exc_info=True)
    closedb()
    gc.collect()

结果:

this logfile : H:/PO/worklog/pylibaryora_2020-05-31_18_55_42.log is create by programer :pylibaryora
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - inner - INFO - Start doing getword and detail parameter: No parameter 
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - getword - INFO - word content is: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!"#$%&'( )*+,-./:;<=>?@[]^_`{|}~ and total with 94
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - inner - INFO - Execute getword using times: 0:00:00s
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - inner - INFO - getword is done successfully with detail parameter: No parameter '
'
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - inner - INFO - Start doing condb and detail parameter: No parameter 
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - inner - INFO - Execute condb using times: 0:00:00.000495s
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - inner - INFO - condb is done successfully with detail parameter: No parameter '
'
31-05-2020 18:55:42 - main - 20 - pylibaryora.py - inner - INFO - Start doing executesql and detail parameter: ('drop table librarykey purge',) 
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - Execute executesql using times: 0:00:00.175587s
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - executesql is done successfully with detail parameter: ('drop table librarykey purge',) '
'
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - Start doing executesql and detail parameter: ('create table librarykey ( wordkey varchar(20),CONSTRAINT work_key primary key (wordkey))',) 
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - Execute executesql using times: 0:00:00.032737s
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - executesql is done successfully with detail parameter: ('create table librarykey ( wordkey varchar(20),CONSTRAINT work_key primary key (wordkey))',) '
'
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - Start doing executemanysql and detail parameter: ('insert into librarykey (wordkey) values (:wordkey)',) 
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - executemanysql - INFO - Start batch execute insert into librarykey (wordkey) values (:wordkey)
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - Start doing threefor and detail parameter: No parameter 
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - Execute threefor using times: 0:00:00.290166s
31-05-2020 18:55:43 - main - 20 - pylibaryora.py - inner - INFO - threefor is done successfully with detail parameter: No parameter '
'
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - Execute executemanysql using times: 0:00:04.957653s
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - executemanysql is done successfully with detail parameter: ('insert into librarykey (wordkey) values (:wordkey)',) '
'
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - Start doing executesql and detail parameter: ('select count(*) from  librarykey',) 
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - Execute executesql using times: 0:00:00.009921s
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - executesql is done successfully with detail parameter: ('select count(*) from  librarykey',) '
'
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - <module> - INFO - total password number should be 830584:
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - Start doing sqlcommit and detail parameter: No parameter 
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - Execute sqlcommit using times: 0:00:00.001492s
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - sqlcommit is done successfully with detail parameter: No parameter '
'
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - Start doing closedb and detail parameter: No parameter 
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - Execute closedb using times: 0:00:00.001488s
31-05-2020 18:55:48 - main - 20 - pylibaryora.py - inner - INFO - closedb is done successfully with detail parameter: No parameter '
'

Process finished with exit code 0