def __init__(self, config):
try:
self.DataName = config['datatype']
del config['datatype']
except:
self.DataName = 'MYSQL'
if self.DataName == 'MYSQL':
try:
self.pool = mysql.connector.pooling.MySQLConnectionPool(
**config)
self.cnx = self.cur = None
except mysql.connector.Error as err:
# 这里需要记录操作日志
logging.debug(err.msg)
self.cnx = None
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'POSTGRESQL':
try:
self.pool = SimpleConnectionPool(**config)
except:
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'ORACLE':
try:
if config['NLS_LANG']:
os.environ['NLS_LANG'] = config['NLS_LANG']
del config['NLS_LANG']
except:
pass
try:
self.pool = cx_Oracle.SessionPool(**config)
except:
raise BaseError(701) # 与数据库连接异常
class xDB(object):
def __init__(self):
self.pgpool = SimpleConnectionPool(minconn=1, maxconn=10, host='localhost', database='p2pworld',
user='******',
password='******', port=5432)
self._db = None
self.trans = {}
def __del__(self):
self.pgpool.closeall()
#执行查询，返回结果list
def Query(self, sqlstr):
rv = {}
try:
db = self.pgpool.getconn()
db.autocommit = True
cur = db.cursor(cursor_factory=RealDictCursor)
cur.execute(sqlstr)
_fetch = cur.fetchall()
if len(_fetch) == 0:
rv['error'] = 800
rv['result'] = 'result NULL'
return rv
rv['error'] = 0
rv['result'] = _fetch
except Exception, e:
rv['error'] = 800
rv['result'] = 'ERR: %s' % (repr(e))
finally:
def __init__(self,
gp_host,
gp_port,
gp_dbname,
gp_user,
password,
minconn=1,
maxconn=5,
multithreading=True):
self.host = gp_host
self.port = gp_port
self.dbname = gp_dbname
self.user = gp_user
self.password = password
if multithreading:
# 可用在多线程应用程序中
self.gp_pool = ThreadedConnectionPool(minconn,
maxconn,
host=gp_host,
port=gp_port,
dbname=gp_dbname,
user=gp_user,
password=password)
else:
# 仅用于单线程应用程序中
self.gp_pool = SimpleConnectionPool(minconn,
maxconn,
host=gp_host,
port=gp_port,
dbname=gp_dbname,
user=gp_user,
password=password)
def __init__(self):
self.pool = SimpleConnectionPool(1, 10,
dbname=os.environ['INFRABOX_DATABASE_DB'],
user=os.environ['INFRABOX_DATABASE_USER'],
password=os.environ['INFRABOX_DATABASE_PASSWORD'],
host=os.environ['INFRABOX_DATABASE_HOST'],
port=os.environ['INFRABOX_DATABASE_PORT'])
class PGDbHelper(object):
def __init__(self, conn_str, echo=False):
self.echo = echo
self.pool = SimpleConnectionPool(1, 12, conn_str)
def finish(self):
self.pool.closeall()
@contextmanager
def _get_cursor(self):
conn = self.pool.getconn()
# conn.autocommit = True
conn.set_isolation_level(0)
try:
if self.echo:
cur = conn.cursor(cursor_factory=LoggingCursor)
else:
cur = conn.cursor(cursor_factory=DictCursor)
yield cur
conn.commit()
conn.close()
finally:
self.pool.putconn(conn)
def insert(self, sql_string, value):
try:
with self._get_cursor() as cur:
cur.execute(sql_string, value)
except Exception, ex:
logger.debug("Error while executing %s" % sql_string)
logger.debug(traceback.print_exc())
raise ex
def get_conn(self, key=None):
if key:
if not self.pool:
self.pool = SimpleConnectionPool(**config.PostgresqlDbConfig)
self.conn = self.pool.getconn(key)
else:
self.conn = connect(**config_conn)
self.conn.autocommit = False
def __init__(self):
self.db_conn_pool = SimpleConnectionPool(2,
3,
host=DST_DB_HOST,
port=int(DST_DB_PORT),
user=DST_DB_USER,
password=DST_DB_PASSWORD,
database=DST_DB_DATABASE)
def __init__(self, config):
self.logger = logging.getLogger('postgres_connector')
try:
self.pool = SimpleConnectionPool(1, 10, **config)
self.schema = 'entity_lookup'
except Exception as ex:
self.logger.exception(
'Exception occurred while connecting to the database')
raise ex
def get_conn():
global POOL
if not POOL:
POOL = SimpleConnectionPool(POOL_SIZE, POOL_SIZE, "")
conn = POOL.getconn()
try:
yield conn
finally:
POOL.putconn(conn)
def __init__(self, dbconfig):
self.dbconfig = dbconfig
self.db = SimpleConnectionPool(minconn=self.dbconfig['minconn'],
maxconn=self.dbconfig['maxconn'],
database=self.dbconfig['dbname'],
user=self.dbconfig['username'],
host=self.dbconfig['host'],
port=self.dbconfig['port'],
password=self.dbconfig['password'])
def __init__(self, conn_num=3):
self.__init_logger()
self.log.info("Creating pool")
self.conn_num = conn_num
self.conn = psycopg2.connect(self.LOCALHOST_STING)
self.cur = self.conn.cursor()
self.pool = SimpleConnectionPool(self.conn_num, self.conn_num + 5,
self.LOCALHOST_STING)
self.register_adapters()
self.create_table(False)
def __init__(self, username: str, password: str, public=False):
self.cp = SimpleConnectionPool(1,
5,
user="******",
password="******",
host="sh.wtd2.top",
port="5432",
database="12306")
if not public:
self.password_check(username, password.encode('utf-8'))
self.passenger_info()
class PostgresPoolWrapper:
def __init__(self,
postgres_dsn: str,
min_connections: int = int(os.environ["MIN_DB_CONNECTIONS"]),
max_connections: int = int(os.environ["MAX_DB_CONNECTIONS"])):
self.postgres_pool: Optional[SimpleConnectionPool] = None
self.postgres_dsn = postgres_dsn
self.min_connections = min_connections
self.max_connections = max_connections
def init(self):
""" Connects to the database and initializes connection pool """
if self.postgres_pool is not None:
return
try:
self.postgres_pool = SimpleConnectionPool(
self.min_connections,
self.max_connections,
self.postgres_dsn,
cursor_factory=RealDictCursor)
if self.postgres_pool is None:
raise Exception("Unknown error")
except (Exception, psycopg2.DatabaseError) as e:
print(f"Failed to create Postgres connection pool: {e}")
def get_conn(self) -> Iterator[RealDictConnection]:
""" Yields a connection from the connection pool and returns the connection to the pool
after the yield completes
"""
if self.postgres_pool is None:
raise Exception(
"Cannot get db connection before connecting to database")
conn: RealDictConnection = self.postgres_pool.getconn()
if conn is None:
raise Exception(
"Failed to get connection from Postgres connection pool")
yield conn
self.postgres_pool.putconn(conn)
def cleanup(self):
""" Closes all connections in the connection pool """
if self.postgres_pool is None:
return
self.postgres_pool.closeall()
def connect(self):
""" Initialize database connection pool """
try:
self.conn_pool = SimpleConnectionPool(minconn=1,
maxconn=self.pool_max_size,
user=self.user,
password=self.password,
host=self.host,
port=self.port,
database=self.database)
except (Exception, psycopg2.Error) as error:
logger.fatal(f'Error connecting to PostgreSQL: {error}')
def __init__(self):
try:
self.connectPool = SimpleConnectionPool(2,
20,
host=POSTGRE_HOST,
port=POSTGRE_PORT,
user=POSTGRE_USER,
password=POSTGRE_PASSWORD,
database=POSTGRE_DATABASE,
keepalives=1,
keepalives_idle=30,
keepalives_interval=10,
keepalives_count=5)
except Exception as e:
print(e)
async def main():
pool = SimpleConnectionPool(1, 10, dbConnection)
conn = pool.getconn()
cursor = conn.cursor()
async for orderbook in poll():
try:
orderbook_pair = orderbook.get('result', {}).get(pair, {})
for item in orderbook_pair['bids']:
print(item)
print()
for item in orderbook_pair['asks']:
print(item)
finally:
conn.close()
def connect_to_db(self):
db_name = self.db_name
cp = configparser.ConfigParser()
cp.read(self.config_file)
password = cp.get(db_name, "password")
user = cp.get(db_name, "user")
database = cp.get(db_name, "database")
host = cp.get(db_name, "host")
port = cp.get(db_name, "port")
kwargs = {"host":host,"password":password,
"user":user,"dbname":database, "port":port}
self.conn_pool = SimpleConnectionPool(1, 3, **kwargs)
def postgres_demo(request):
global pg_pool
# Initialize the pool lazily, in case SQL access isn't needed for this
# GCF instance. Doing so minimizes the number of active SQL connections,
# which helps keep your GCF instances under SQL connection limits.
if not pg_pool:
pg_pool = SimpleConnectionPool(1, 1, **pg_config)
# Remember to close SQL resources declared while running this function.
# Keep any declared in global scope (e.g. pg_pool) for later reuse.
with pg_pool.getconn().cursor() as cursor:
cursor.execute('SELECT NOW() as now')
results = cursor.fetchone()
return str(results[0])
def create_pool():
global pool
if pool is None:
pool = SimpleConnectionPool(app.config["MINCONN"],
app.config["MAXCONN"],
dsn=get_db_connection_string())
return pool
def __connect(host):
"""
Helper function to connect to Postgres
"""
global PG_POOL
PG_CONFIG['host'] = host
PG_POOL = SimpleConnectionPool(1, 1, **PG_CONFIG)
def api_app(config_class):
""" Initialize main app object."""
app = Flask(__name__)
app.config.from_object(config_class)
app.logger.info(f'Creating API server with {app.config["ENV"]}')
CORS(app)
app.before_request_funcs = {None: [token_auth, db_connection]}
app.teardown_appcontext_funcs = [shutdown_session]
# for key, value in exception_handlers.items():
# app.register_error_handler(key, value)
from rpserver.rider import rider_bp
from rpserver.spot import spot_bp
from rpserver.event import event_bp
app.register_blueprint(rider_bp, url_prefix='/riders')
app.register_blueprint(spot_bp, url_prefix='/spots')
app.register_blueprint(event_bp, url_prefix='/events')
app.db_connection_pool = SimpleConnectionPool(
app.config['DB_CONNECTIONS_MIN'],
app.config['DB_CONNECTIONS_MAX'],
app.config['DB_SERVER_URI'],
cursor_factory=DictCursor)
return app
class db:
params = config()
# str = connect(**params)
str_1 = SimpleConnectionPool(minconn=1,maxconn=5,**params)
str = str_1.getconn()
def __init__(self):
self._conn = connect(**params)
self._cur= self._conn.cursor()
@property
def connection(self):
return self._conn
@property
def cursor(self):
return self._cur
def commit(self):
self.connection.commit()
def execute(self,sql):
return self.cursor.execute(sql)
def fetchall(self):
return self.cursor.fetchall()
def query(self, sql):
self.cursor.execute(sql)
return self.fetchall()
def openConn():
global conn
global cur
# conn = psycopg2.connect(database="huhula", user="******", host="roachdb", port=26257)
# cs="postgresql://huhuladb00:26257/mydb?user=root&sslcert=/Users/ivasilchikov/spot/certs/client.root.crt&sslkey=/Users/ivasilchikov/spot/certs/client.root.key&sslmode=require&ssl=true"
cs = "postgresql://huhuladb00,huhuladb01,huhuladb02:26257/huhula?user=huhulaman&sslcert=/home/ubuntu/spot/certs/client.huhulaman.crt&sslkey=/home/ubuntu/spot/certs/client.huhulaman.key&sslmode=require&ssl=true"
# conn = psycopg2.connect(cs)
global g_pool
g_pool = SimpleConnectionPool(3, 7, cs)
con = g_pool.getconn()
con.set_session(autocommit=True)
cur = con.cursor()
cur.execute("show databases;")
res = cur.fetchall()
print "res=" + str(res)
class InfraBoxPostgresPlugin(object):
name = 'ibpostgres'
def __init__(self):
self.pool = SimpleConnectionPool(1, 10,
dbname=os.environ['INFRABOX_DATABASE_DB'],
user=os.environ['INFRABOX_DATABASE_USER'],
password=os.environ['INFRABOX_DATABASE_PASSWORD'],
host=os.environ['INFRABOX_DATABASE_HOST'],
port=os.environ['INFRABOX_DATABASE_PORT'])
def apply(self, callback, context):
# Test if the original callback accepts a 'conn' keyword.
# Ignore it if it does not need a database connection.
args = inspect.getargspec(context['callback'])[0]
if 'conn' not in args:
return callback
def wrapper(*args, **kwargs):
# Connect to the database
conn = None
try:
conn = self.pool.getconn()
except HTTPResponse, e:
raise HTTPError(500, "Database Error", e)
# Add the connection handle as a keyword argument.
kwargs['conn'] = conn
try:
rv = callback(*args, **kwargs)
except HTTPError, e:
raise
except HTTPResponse, e:
raise
def __connect(host):
"""
Helper function to connect to Postgres
"""
global pg_pool
pg_config['host'] = host
pg_pool = SimpleConnectionPool(1, 1, **pg_config)
def __init__(self, database, host=None, port=None, user=None,
password=None, client_encoding="utf8",
minconn=1, maxconn=5,
**kwargs):
self.host = "%s:%s" % (host, port)
_db_args = dict(
async=True,
database=database,
client_encoding=client_encoding,
**kwargs
)
if host is not None:
_db_args["host"] = host
if port is not None:
_db_args["port"] = port
if user is not None:
_db_args["user"] = user
if password is not None:
_db_args["password"] = password
try:
self._pool = SimpleConnectionPool(
minconn=minconn, maxconn=maxconn, **_db_args)
except Exception:
logging.error("Cannot connect to PostgreSQL on %s", self.host,
exc_info=True)
def __pg_pool(cls):
"""
ConnectionPool for Postgres governed by psycopg2.
:return:
"""
if cls.__pg_connection_pool is None:
try:
connection_dialect = cls.__connection_dialects['postgresql']
dsn = "dbname='{}' user='******' host='{}' password='******' port='{}'".format(
connection_dialect['database'], connection_dialect['user'],
connection_dialect['host'], connection_dialect['password'],
connection_dialect['port'])
# connection_pool with 25 live connections. Tweak this according to convenience.
cls.__pg_connection_pool = SimpleConnectionPool(1, 25, dsn=dsn)
cls.connection_manager_logger.info(
'[connection_manager]: PG Pool class method is invoked for first time. '
'PG Pool will be initialized for Postgres engine of PROTON.'
)
except Exception as e:
cls.connection_manager_logger.info(
'[connection_manager]: Error creating a PG Pool. Stack trace to follow.'
)
cls.connection_manager_logger.exception(str(e))
else:
cls.connection_manager_logger.info(
'[connection_manager]: Request for PG Pool method is invoked subsequently. '
'PG Pool previously initialized for all PROTON supported engines is returned.'
)
return cls.__pg_connection_pool
def __init__(self,config):
try :
self.DataName=config['datatype']
del config['datatype']
except:
self.DataName='MYSQL'
if self.DataName == 'MYSQL' :
try:
self.pool = mysql.connector.pooling.MySQLConnectionPool(**config)
self.cnx=self.cur=None
except mysql.connector.Error as err:
# 这里需要记录操作日志
logging.debug(err.msg)
self.cnx=None
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'POSTGRESQL' :
try :
self.pool = SimpleConnectionPool(**config)
except:
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'ORACLE' :
try :
if config['NLS_LANG'] :
os.environ['NLS_LANG']=config['NLS_LANG']
del config['NLS_LANG']
except:
pass
try :
self.pool = cx_Oracle.SessionPool(**config)
except :
raise BaseError(701) # 与数据库连接异常
def pool():
try:
params = dbpoolconfig()
pool = SimpleConnectionPool(**params)
print("!!!!!!!!!!!! pool() !!!!!!!!!!!!")
return pool
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def create_pool():
global pool
if pool is None:
_preparedb()
pool = SimpleConnectionPool(app.config["MINCONN"],
app.config["MAXCONN"],
dsn=db_connection_string)
return pool
def connect_pool(self,host,port,user,password,database,schema='public'):
try:
options = f'-c search_path={schema}'
pool = SimpleConnectionPool(
minconn=1,
maxconn=1,
host=host,
port=port,
user=user,
password=password,
database=database,
options=options
)
self._client = pool.getconn()
except Exception as e:
print('error:',repr(e))
raise e
def init(self):
""" Connects to the database and initializes connection pool """
if self.postgres_pool is not None:
return
try:
self.postgres_pool = SimpleConnectionPool(
self.min_connections,
self.max_connections,
self.postgres_dsn,
cursor_factory=RealDictCursor)
if self.postgres_pool is None:
raise Exception("Unknown error")
except (Exception, psycopg2.DatabaseError) as e:
print(f"Failed to create Postgres connection pool: {e}")
def initialize(cls, **kwargs):
cls.__pool = SimpleConnectionPool(1, 20,
user = db_user,
password = db_password,
host = db_host,
port = db_port,
database = db_database)
print("database conn initialize")
def __init__(self, dbconfig):
self.dbconfig = dbconfig
self.db = SimpleConnectionPool(
minconn=self.dbconfig['minconn'],
maxconn=self.dbconfig['maxconn'],
database=self.dbconfig['dbname'],
user=self.dbconfig['username'],
host=self.dbconfig['host'],
port=self.dbconfig['port'],
password=self.dbconfig['password'])
class Datastore(object):
"""
the datastore interface
"""
def __init__(self, dbname='web', dbuser='******', dbpassw='Klofcumad1'):
self.db = dbname
self.dbuser = dbuser
self.dbpassw = dbpassw
self.pool = SimpleConnectionPool(1, 100, database=dbname, user=dbuser, password=dbpassw)
def start_op(self):
conn = self.pool.getconn()
cur = conn.cursor()
return (conn, cur)
def close_op(self, conn):
conn.commit()
self.pool.putconn(conn)
def find_user(self, id=None, email=None):
assert(id != None or email != None)
conn, cur = self.start_op()
if id != None:
cur.execute("SELECT * FROM users WHERE id=%s", [id])
if email != None:
cur.execute("SELECT * FROM users WHERE email=%s", [email])
row = cur.fetchone()
print(row)
if row != None:
return User(id=row[1], email=row[0], passw=row[2])
return None
def add_user(self, email, password):
assert(email != None and password != None)
conn, cur = self.start_op()
id = uuid()
cur.execute("INSERT INTO users (id, email, password) VALUES (%s, %s, %s)", [str(id), email, generate_password_hash(password)])
self.close_op(conn)
return id
class DBService:
def __init__(self, dbconfig):
self.dbconfig = dbconfig
self.db = SimpleConnectionPool(
minconn=self.dbconfig['minconn'],
maxconn=self.dbconfig['maxconn'],
database=self.dbconfig['dbname'],
user=self.dbconfig['username'],
host=self.dbconfig['host'],
port=self.dbconfig['port'],
password=self.dbconfig['password'])
@contextmanager
def get_cursor(self):
con = self.db.getconn()
try:
yield con.cursor()
con.commit()
finally:
self.db.putconn(con)
def update(self, sql):
with self.get_cursor() as cursor:
logging.debug(u'Executing sql: [{0}]'.format(sql.strip()))
cursor.execute(sql)
def query(self, sql, rowCallback):
with self.get_cursor() as cursor:
logging.debug(u'Executing sql: [{0}]'.format(sql.strip()))
cursor.execute(sql)
for row in cursor:
rowCallback(row)
def query_single(self, sql):
with self.get_cursor() as cursor:
logging.debug(u'Executing sql: [{0}]'.format(sql.strip()))
cursor.execute(sql)
yield cursor.fetchone()
def __init__(self):
self.pgpool = SimpleConnectionPool(minconn=1, maxconn=10, host='localhost', database='p2pworld',
user='******',
password='******', port=5432)
self._db = None
self.trans = {}
host = parsed.hostname
port = parsed.port
if port is None:
port = '5432' # postgres default port
dsn = "dbname={} host={} port={}".format(dbname, host, port)
if user:
dsn += ' username={}'.format(user)
if password:
dsn += ' password={}'.format(password)
return dsn
if __name__ == "__main__":
# Getting dsn from console arguments
# postgres://user:password@localhost:5432/test_erp
if 'postgres' not in urlparse.uses_netloc:
# Teach urlparse about postgres:// URLs.
urlparse.uses_netloc.append('postgres')
if len(sys.argv) > 1:
conn_string = url_to_dsn(sys.argv[1])
else:
conn_string = url_to_dsn("postgres://localhost:5432/test_erp")
# creating pool
pool = SimpleConnectionPool(1, 5, dsn=conn_string)
for i in xrange(1,6):
print "Question {}:\n\r{}".format(i, getattr(sys.modules[__name__], 'question{}'.format(i)).__doc__)
conn = pool.getconn()
print getattr(sys.modules[__name__], 'question{}'.format(i))(conn)
pool.putconn(conn)
print "="*20
pool.closeall()
class Connection(object):
""""""
def __init__(self, database, host=None, port=None, user=None,
password=None, client_encoding="utf8",
minconn=1, maxconn=5,
**kwargs):
self.host = "%s:%s" % (host, port)
_db_args = dict(
async=True,
database=database,
client_encoding=client_encoding,
**kwargs
)
if host is not None:
_db_args["host"] = host
if port is not None:
_db_args["port"] = port
if user is not None:
_db_args["user"] = user
if password is not None:
_db_args["password"] = password
try:
self._pool = SimpleConnectionPool(
minconn=minconn, maxconn=maxconn, **_db_args)
except Exception:
logging.error("Cannot connect to PostgreSQL on %s", self.host,
exc_info=True)
def __del__(self):
self._pool.closeall()
def _connect(self, callback=None):
"""Get an existing database connection."""
conn = self._pool.getconn()
callback = functools.partial(callback, conn)
Poller(conn, (callback, ))._update_handler()
@gen.coroutine
def _cursor(self):
conn = yield gen.Task(self._connect)
cursor = conn.cursor()
raise gen.Return(cursor)
def putconn(self, conn, close=False):
self._pool.putconn(conn, close=close)
@gen.coroutine
def query(self, query, parameters=()):
"""Returns a row list for the given query and parameters."""
cursor = yield self._cursor()
try:
yield gen.Task(self._execute, cursor, query, parameters)
column_names = [d[0] for d in cursor.description]
raise gen.Return([Row(zip(column_names, row)) for row in cursor])
finally:
self.putconn(cursor.connection)
cursor.close()
@gen.coroutine
def get(self, query, parameters=()):
"""Returns the (singular) row returned by the given query.
If the query has no results, returns None. If it has
more than one result, raises an exception.
"""
rows = yield self.query(query, parameters)
if not rows:
raise gen.Return(None)
elif len(rows) > 1:
raise Exception("Multiple rows returned for Database.get() query")
else:
raise gen.Return(rows[0])
@gen.coroutine
def execute(self, query, parameters=()):
"""Executes the given query."""
cursor = yield self._cursor()
try:
yield gen.Task(self._execute, cursor, query, parameters)
finally:
self.putconn(cursor.connection)
cursor.close()
def _execute(self, cursor, query, parameters, callback=None):
if not isinstance(parameters, (tuple, list)):
raise
try:
cursor.execute(query, parameters)
Poller(cursor.connection, (callback,))._update_handler()
except psycopg2.OperationalError:
logging.error("Error connecting to PostgreSQL on %s", self.host)
self.putconn(cursor.connection, close=True)
def __init__(self, dbname='web', dbuser='******', dbpassw='Klofcumad1'):
self.db = dbname
self.dbuser = dbuser
self.dbpassw = dbpassw
self.pool = SimpleConnectionPool(1, 100, database=dbname, user=dbuser, password=dbpassw)
class DB(object):
def __init__(self,config):
try :
self.DataName=config['datatype']
del config['datatype']
except:
self.DataName='MYSQL'
if self.DataName == 'MYSQL' :
try:
self.pool = mysql.connector.pooling.MySQLConnectionPool(**config)
self.cnx=self.cur=None
except mysql.connector.Error as err:
# 这里需要记录操作日志
logging.debug(err.msg)
self.cnx=None
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'POSTGRESQL' :
try :
self.pool = SimpleConnectionPool(**config)
except:
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'ORACLE' :
try :
if config['NLS_LANG'] :
os.environ['NLS_LANG']=config['NLS_LANG']
del config['NLS_LANG']
except:
pass
try :
self.pool = cx_Oracle.SessionPool(**config)
except :
raise BaseError(701) # 与数据库连接异常
def open(self):
try :
if self.DataName=='ORACLE' :
self.__conn = self.pool.acquire()
self.__cursor = self.__conn.cursor()
elif self.DataName=='POSTGRESQL' :
self.__conn = self.pool.getconn()
self.__cursor = self.__conn.cursor()
else : # 默认为Mysql
self.__conn = self.pool.get_connection()
self.__cursor = self.__conn.cursor(buffered=True)
#self.__conn.autocommit=True
self.__conn.autocommit=False
self.cnx=self.__conn
self.cur=self.__cursor
except :
raise BaseError(702) # 无法获得连接池
def close(self):
#关闭游标和数据库连接
self.__conn.commit()
if self.__cursor is not None:
self.__cursor.close()
if self.DataName == 'POSTGRESQL' :
self.pool.putconn(self.__conn)#将数据库连接放回连接池中
else :
self.__conn.close()
def begin(self):
self.__conn.autocommit=False
def commit(self):
self.__conn.commit()
def rollback(self):
self.__conn.rollback()
#---------------------------------------------------------------------------
def findBySql(self,sql,params = {},limit = 0,join = 'AND',lock=False):
"""
自定义sql语句查找
limit = 是否需要返回多少行
params = dict(field=value)
join = 'AND | OR'
"""
try :
cursor = self.__getCursor()
sql = self.__joinWhere(sql,params,join)
cursor.execute(sql,tuple(params.values()))
rows = cursor.fetchmany(size=limit) if limit > 0 else cursor.fetchall()
result = [dict(zip(cursor.column_names,row)) for row in rows] if rows else None
return result
except:
raise BaseError(706)
def countBySql(self,sql,params = {},join = 'AND'):
# 自定义sql 统计影响行数
try:
cursor = self.__getCursor()
sql = self.__joinWhere(sql,params,join)
cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone();
return result[0] if result else 0
except:
raise BaseError(707)
#def updateByPk(self,table,data,id,pk='id'):
# # 根据主键更新，默认是id为主键
# return self.updateByAttr(table,data,{pk:id})
def deleteByAttr(self,table,params={},join='AND'):
# 删除数据
try :
fields = ','.join(k+'=%s' for k in params.keys())
sql = "DELETE FROM `%s` "% table
sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
cursor.execute(sql,tuple(params.values()))
self.__conn.commit()
return cursor.rowcount
#except:
# raise BaseError(704)
except Exception as err:
raise BaseError(704,err._full_msg)
def deleteByPk(self,table,id,pk='id'):
# 根据主键删除，默认是id为主键
return self.deleteByAttr(table,{pk:id})
def findByAttr(self,table,criteria = {}):
# 根据条件查找一条记录
return self.__query(table,criteria)
def findByPk(self,table,id,pk='id'):
return self.findByAttr(table,{'where':pk+'='+str(id)})
def findAllByAttr(self,table,criteria={}):
# 根据条件查找记录
return self.__query(table,criteria,True)
def exit(self,table,params={},join='AND'):
# 判断是否存在
return self.count(table,params,join) > 0
# 公共的方法 -------------------------------------------------------------------------------------
def count(self,table,params={},join='AND'):
# 根据条件统计行数
try :
sql = 'SELECT COUNT(*) FROM %s' % table
if params :
where ,whereValues = self.__contact_where(params)
sqlWhere= ' WHERE '+where if where else ''
sql+=sqlWhere
#sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
self.__display_Debug_IO(sql,tuple(whereValues)) #DEBUG
if self.DataName=='ORACLE':
cursor.execute(sql % tuple(whereValues))
else :
cursor.execute(sql,tuple(whereValues))
#cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone();
return result[0] if result else 0
#except:
# raise BaseError(707)
except Exception as err:
try :
raise BaseError(707,err._full_msg)
except :
raise BaseError(707)
def getToListByPk(self,table,criteria={},id=None,pk='id'):
# 根据条件查找记录返回List
if ('where' not in criteria) and (id is not None) :
criteria['where']=pk+ "='" + str(id) + "'"
return self.__query(table,criteria,isDict=False)
def getAllToList(self,table,criteria={},id=None,pk='id',join='AND'):
# 根据条件查找记录返回List
if ('where' not in criteria) and (id is not None) :
criteria['where']=pk+ "='" + str(id) + "'"
return self.__query(table,criteria,all=True,isDict=False)
def getToObjectByPk(self,table,criteria={},id=None,pk='id'):
# 根据条件查找记录返回Object
if ('where' not in criteria) and (id is not None) :
criteria['where']=pk+"='"+str(id)+"'"
return self.__query(table,criteria)
def getAllToObject(self,table,criteria={},id=None,pk='id',join='AND'):
# 根据条件查找记录返回Object
if ('where' not in criteria) and (id is not None) :
criteria['where']=pk+"='"+str(id)+"'"
return self.__query(table,criteria,all=True)
def insert(self,table,data,commit=True):
# 新增一条记录
try :
'''
从data中分离含用SQL函数的字字段到funData字典中,
不含SQL函数的字段到newData
'''
funData,newData=self.__split_expression(data)
funFields='';funValues=''
# 拼不含SQL函数的字段及值
fields = ','.join(k for k in newData.keys())
values = ','.join(("%s", ) * len(newData))
# 拼含SQL函数的字段及值
if funData :
funFields = ','.join(k for k in funData.keys())
funValues =','.join( v for v in funData.values())
# 合并所有字段及值
fields += ','+funFields if funFields else ''
values += ','+funValues if funValues else ''
sql = 'INSERT INTO %s (%s) VALUES (%s)'%(table,fields,values)
cursor = self.__getCursor()
for (k,v) in newData.items() :
try:
if isinstance(v, str) :
newData[k]="'%s'" % (v,)
except :
pass
self.__display_Debug_IO(sql,tuple(newData.values())) #DEBUG
sql= sql % tuple(newData.values())
if self.DataName=='POSTGRESQL' :
sql+=' RETURNING id'
cursor.execute(sql)
#if self.DataName=='ORACLE':
#sql= sql % tuple(newData.values())
#cursor.execute(sql)
#else :
#cursor.execute(sql,tuple(newData.values()))
if self.DataName=='ORACLE':
# 1. commit 一定要为假
# 2. Oracle Sequence 的命名规范为： [用户名.]SEQ_表名_ID
# 3. 每张主表都应该有ID
t_list=table.split('.')
if len(t_list)>1 :
SEQ_Name= t_list[0]+'.SEQ_'+t_list[1]+'_ID'
else :
SEQ_Name='SEQ_'+t_list[0]+'_ID'
cursor.execute('SELECT %s.CURRVAL FROM dual' % SEQ_Name.upper())
result = cursor.fetchone()
insert_id= result[0] if result else 0
#insert_id=cursor.rowcount
elif self.DataName=='MYSQL' :
insert_id = cursor.lastrowid
elif self.DataName=='POSTGRESQL':
item = cursor.fetchone()
insert_id = item[0]
if commit : self.commit()
return insert_id
except Exception as err:
try :
raise BaseError(705,err._full_msg)
except :
raise BaseError(705,err.args)
def update(self,table,data,params={},join='AND',commit=True,lock=True):
# 更新数据
try :
fields,values = self.__contact_fields(data)
if params :
where ,whereValues = self.__contact_where(params)
values.extend(whereValues) if whereValues else values
sqlWhere= ' WHERE '+where if where else ''
cursor = self.__getCursor()
if commit : self.begin()
if lock :
sqlSelect="SELECT %s From %s %s for update" % (','.join(tuple(list(params.keys()))),table,sqlWhere)
sqlSelect=sqlSelect % tuple(whereValues)
cursor.execute(sqlSelect) # 加行锁
#cursor.execute(sqlSelect,tuple(whereValues)) # 加行锁
sqlUpdate = "UPDATE %s SET %s "% (table,fields) + sqlWhere
for index,val in enumerate(values):
if isinstance(val,str) :
values[index]="'"+val+"'"
self.__display_Debug_IO(sqlUpdate,tuple(values)) #DEBUG
sqlUpdate = sqlUpdate % tuple(values)
cursor.execute(sqlUpdate)
#cursor.execute(sqlUpdate,tuple(values))
if commit : self.commit()
return cursor.rowcount
except Exception as err:
try :
raise BaseError(705,err._full_msg)
except :
raise BaseError(705,err.args)
def updateByPk(self,table,data,id,pk='id',commit=True,lock=True):
# 根据主键更新，默认是id为主键
return self.update(table,data,{pk:id},commit=commit,lock=lock)
def delete(self,table,params={},join='AND',commit=True,lock=True):
# 更新数据
try :
data={}
fields,values = self.__contact_fields(data)
if params :
where ,whereValues = self.__contact_where(params)
values.extend(whereValues) if whereValues else values
sqlWhere= ' WHERE '+where if where else ''
cursor = self.__getCursor()
if commit : self.begin()
#if lock :
#sqlSelect="SELECT %s From %s %s for update" % (','.join(tuple(list(params.keys()))),table,sqlWhere)
#sqlSelect=sqlSelect % tuple(whereValues)
#cursor.execute(sqlSelect) # 加行锁
##cursor.execute(sqlSelect,tuple(whereValues)) # 加行锁
sqlDelete = "DELETE FROM %s %s"% (table,sqlWhere)
for index,val in enumerate(values):
if isinstance(val,str) :
values[index]="'"+val+"'"
self.__display_Debug_IO(sqlDelete,tuple(values)) #DEBUG
sqlDelete = sqlDelete % tuple(values)
cursor.execute(sqlDelete)
#cursor.execute(sqlUpdate,tuple(values))
if commit : self.commit()
return cursor.rowcount
except Exception as err:
try :
raise BaseError(705,err._full_msg)
except :
raise BaseError(705,err.args)
def deleteByPk(self,table,id,pk='id',commit=True,lock=True):
# 根据主键更新，默认是id为主键
return self.delete(table,{pk:id},commit=commit,lock=lock)
# 内部私有的方法 -------------------------------------------------------------------------------------
def __display_Debug_IO(self,sql,params) :
if DEBUG :
debug_now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print('[S '+debug_now_time+' SQL:] '+(sql % params) if params else sql)
def __get_connection(self):
return self.pool.get_connection()
def __getCursor(self):
"""获取游标"""
if self.__cursor is None:
self.__cursor = self.__conn.cursor()
return self.__cursor
def getCursor(self):
"""获取游标"""
if self.__cursor is None:
self.__cursor = self.__conn.cursor()
return self.__cursor
def __joinWhere(self,sql,params,join):
# 转换params为where连接语句
if params:
funParams={};newParams={};newWhere='';funWhere=''
# 从params中分离含用SQL函数的字字段到Params字典中
for (k,v) in params.items():
if 'str' in str(type(v)) and '{{' == v[:2] and '}}'==v[-2:] :
funParams[k]=v[2:-2]
else:
newParams[k]=v
# 拼 newParams 条件
keys,_keys = self.__tParams(newParams)
newWhere = ' AND '.join(k+'='+_k for k,_k in zip(keys,_keys)) if join == 'AND' else ' OR '.join(k+'='+_k for k,_k in zip(keys,_keys))
# 拼 funParams 条件
if funParams :
funWhere = ' AND '.join(k+'='+v for k,v in funParams.items()) if join == 'AND' else ' OR '.join(k+'='+v for k,v in funParams.items())
# 拼最终的 where
where=((newWhere+' AND ' if newWhere else '')+funWhere if funWhere else newWhere) if join=='AND' else ((newWhere+' OR ' if newWhere else '')+funWhere if funWhere else newWhere)
#--------------------------------------
#keys,_keys = self.__tParams(params)
#where = ' AND '.join(k+'='+_k for k,_k in zip(keys,_keys)) if join == 'AND' else ' OR '.join(k+'='+_k for k,_k in zip(keys,_keys))
sql+=' WHERE ' + where
return sql
def __tParams(self,params):
keys = [k if k[:2]!='{{' else k[2:-2] for k in params.keys()]
_keys = ['%s' for k in params.keys()]
return keys,_keys
def __query(self,table,criteria,all=False,isDict=True,join='AND'):
'''
table : 表名
criteria : 查询条件dict
all : 是否返回所有数据，默认为False只返回一条数据,当为真是返回所有数据
isDict : 返回格式是否为字典，默认为True ，即字典否则为数组
'''
try :
if all is not True:
criteria['limit'] = 1 # 只输出一条
sql,params = self.__contact_sql(table,criteria,join) #拼sql及params
'''
# 当Where为多个查询条件时，拼查询条件 key 的 valuse 值
if 'where' in criteria and 'dict' in str(type(criteria['where'])) :
params = criteria['where']
#params = tuple(params.values())
where ,whereValues = self.__contact_where(params)
sql+= ' WHERE '+where if where else ''
params=tuple(whereValues)
else :
params = None
'''
#__contact_where(params,join='AND')
cursor = self.__getCursor()
self.__display_Debug_IO(sql,params) #DEBUG
#if self.DataName=="ORACLE":
#sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % ()
#pass
cursor.execute(sql,params if params else ())
rows = cursor.fetchall() if all else cursor.fetchone()
if isDict :
result = [dict(zip(cursor.column_names,row)) for row in rows] if all else dict(zip(cursor.column_names,rows)) if rows else {}
else :
result = [row for row in rows] if all else rows if rows else []
return result
except Exception as err:
try :
raise BaseError(706,err._full_msg)
except :
raise BaseError(706)
def __contact_sql(self,table,criteria,join='AND'):
sql = 'SELECT '
if criteria and type(criteria) is dict:
#select fields
if 'select' in criteria:
fields = criteria['select'].split(',')
sql+= ','.join(field.strip()[2:-2] if '{{' == field.strip()[:2] and '}}'==field.strip()[-2:] else field.strip() for field in fields)
else:
sql+=' * '
#table
sql+=' FROM %s' % table
#where
whereValues=None
if 'where' in criteria:
if 'str' in str(type(criteria['where'])) : # 当值为String时，即单一Key时
sql+=' WHERE '+ criteria['where']
else : # 当值为dict时，即一组key时
params=criteria['where']
#sql+= self.__joinWhere('',params,join)
#sql+=self.__contact_where(params,join)
where ,whereValues = self.__contact_where(params)
sql+= ' WHERE '+where if where else ''
#sql=sql % tuple(whereValues)
#group by
if 'group' in criteria:
sql+=' GROUP BY '+ criteria['group']
#having
if 'having' in criteria:
sql+=' HAVING '+ criteria['having']
if self.DataName=='MYSQL' :
#order by
if 'order' in criteria:
sql+=' ORDER BY '+ criteria['order']
#limit
if 'limit' in criteria:
sql+=' LIMIT '+ str(criteria['limit'])
#offset
if 'offset' in criteria:
sql+=' OFFSET '+ str(criteria['offset'])
elif (self.DataName=='POSTGRESQL') :
#order by
if 'order' in criteria:
sql+=' ORDER BY '+ criteria['order']
if 'limit' in criteria :
# 取 offset,rowcount
arrLimit=(str(criteria['limit']).split('limit ').pop()).split(',')
strOffset = arrLimit[0]
try :
strRowcount = arrLimit[1]
except :
strOffset = '0'
strRowcount = '1'
sql+=' LIMIT %s OFFSET %s' %(strRowcount,strOffset)
elif (self.DataName=='ORACLE') and ('limit' in criteria) :
# 取 offset,rowcount
arrLimit=(str(criteria['limit']).split('limit ').pop()).split(',')
strOffset = arrLimit[0]
try :
strRowcount = arrLimit[1]
except :
strOffset = '0'
strRowcount = '1'
# 处理 order by
if 'order' in criteria :
strOrder = criteria['order']
else :
strOrder = 'ROWNUM'
# 以下Sql是针对 Oracle 的大数据查询效率
sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % (strOrder,sql,strOffset,strRowcount)
elif (self.DataName=='ORACLE') and ('order' in criteria) :
sql+=' ORDER BY '+ criteria['order']
else:
sql+=' * FROM %s' % table
return sql,whereValues
# 将字符串和表达式分离
def __split_expression(self,data) :
funData={};newData={};funFields=''
# 从data中移出含用SQL函数的字字段到funData字典中
for (k,v) in data.items():
if 'str' in str(type(v)) and '{{' == v[:2] and '}}'==v[-2:] :
funData[k]=v[2:-2]
else : newData[k]=v
return (funData,newData)
# 拼Update字段
def __contact_fields(self,data) :
funData,newData=self.__split_expression(data)
if funData :
funFields = ','.join(k+'=%s' % (v) for k,v in funData.items())
fields = ','.join(k+'=%s' for k in newData.keys())
# fields 与 funFields 合并
if funData :
fields = ','.join([fields,funFields]) if fields else funFields
values = list(newData.values())
return (fields,values)
def __hasKeyword(self,key) :
if '{{}}' in key : return True
if 'in (' in key : return True
if 'like ' in key : return True
if '>' in key : return True
if '<' in key : return True
return False
# 拼Where条件
def __contact_where(self,params,join='AND') :
funParams,newParams=self.__split_expression(params)
# 拼 newParams 条件
keys,_keys = self.__tParams(newParams)
newWhere = ' AND '.join(k+'='+_k for k,_k in zip(keys,_keys)) if join == 'AND' else ' OR '.join(k+'='+_k for k,_k in zip(keys,_keys))
values = list(newParams.values())
# 拼 funParams 条件
#funWhere = ' AND '.join(('`' if k else '') +k+('`' if k else '')+ (' ' if self.__hasKeyword(v) else '=') +v for k,v in funParams.items()) if join == 'AND' else ' OR '.join('`'+k+'`'+(' ' if self.__hasKeyword(v) else '=')+v for k,v in funParams.items())
funWhere = ' AND '.join(k+ (' ' if self.__hasKeyword(v) else '=' if k else '') +v for k,v in funParams.items()) if join == 'AND' else ' OR '.join(k+(' ' if self.__hasKeyword(v) else '=' if k else '')+v for k,v in funParams.items())
# 拼最终的 where
where=((newWhere+' AND ' if newWhere else '')+funWhere if funWhere else newWhere) if join=='AND' else ((newWhere+' OR ' if newWhere else '')+funWhere if funWhere else newWhere)
return (where,values)
def get_ids(self,list): #从getAllToList返回中提取id
try:
test=list[0][0]
dimension=2
except:
dimension=1
ids=[]
if dimension>1 :
for i in range(len(list)) : ids.append(str(list[i][0]))
else :
for i in range(len(list)) : ids.append(str(list[i]))
return ','.join(ids)
