def __init__(self,
host: str,
port: int,
user: str,
password: str,
dbname: str,
minsize: int = 1,
maxsize: int = 10):
"""
:param host: database host ip.
:param port: database port number.
:param user: database user name.
:param password: database password.
:param dbname: database name.
:param minsize: the minimum size of the connection pool.
:param maxsize: the maximum size of the connection pool.
"""
MocaNamedInstance.__init__(self)
MocaClassCache.__init__(self)
self._host: str = host
self._port: int = port
self._user: str = user
self._password: str = password
self._dbname: str = dbname
self._min: int = minsize
self._max: int = maxsize
self._con = Connection(host=host,
port=port,
user=user,
password=password,
db=dbname)
self._aio_con = None
self._aio_pool = None
def init(**options):
configure_logging(json=options['json'], verbose=options['verbose'])
connection = Connection(host=options['mysql_host'],
port=options['mysql_port'],
user=options['mysql_user'],
password=options['mysql_password'],
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
create_db(connection, options['mysql_database'])
connection_pool = PooledDB(
creator=pymysql,
mincached=1,
maxcached=10,
# max connections currently in use - doesn't
# include cached connections
maxconnections=50,
blocking=True,
host=options['mysql_host'],
port=options['mysql_port'],
user=options['mysql_user'],
password=options['mysql_password'],
database=options['mysql_database'],
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
shh_dao = ShhDao(connection_pool)
shh_dao.create_secret_table()
def get_a_new_con(self):
"""Get a new connection."""
return Connection(host=self._host,
port=self._port,
user=self._user,
password=self._password,
db=self._dbname)
def mysql_conn():
# 连接数据库用
conn = Connection(host='localhost',
user='******',
password='******',
port=3306,
database='db1')
cursor = conn.cursor()
# 往名为l的表格中插入姓名和对应年龄
# 插入内容写好后要进行提交
sttr = "大学"
# cursor.execute('drop table aa')
cursor.execute("insert into tb1(name) values (sttr)")
# 数据库事务的提交
conn.commit()
# 测试是否提交成功
print('插入成功！')
# 提取表中第一个内容
# print(cursor.fetchone())
# 如果提取过第一个内容，则是提取前三个
# print(cursor.fetchmany(3))
# 如运行过前两个，则显示除提取后剩下的全部
# print(cursor.fetchall())
# 结束关闭 cursor connection
cursor.close()
conn.close()
def create_pool(size, **kwargs):
logging.debug('create_pool(%r, %r' % (size, kwargs))
global __POOL
kwargs['cursorclass'] = pymysql.cursors.DictCursor
for i in range(size):
__POOL.append(
DBConnection(is_used=False, connection=Connection(**kwargs)))
def pull_data(imei, name): #取数据，传入设备编号与设备名称
print("\n****目前暂不支持跨月查询，请尽量将标准工艺周期安排在同一个月。****\n"
"\n****如果不输入 时:分:秒 的信息，程序将默认为 00:00:00****\n")
start_time = input("\n请输入" + str(name) +
"的标准工艺起始时间（格式“2019-06-17 10:01:23”）：")
end_time = input("\n请输入" + str(name) +
"的标准工艺终止时间（格式“2019-06-17 10:01:23”）：")
year = start_time[0:4]
month = start_time[5:7]
database = "ld_device_data_" + year + month # 数据表的名称
connection_1 = Connection(host='www.xzjn18.com',
user="******",
passwd="tempA13%",
port=8301,
db="data",
charset='utf8') # 连接数据库
sql = "select update_date as time, total_yggl, total_ygdn " \
"from " + database + " " \
"where device_imei = '" + str(
imei) + "' and update_date between '" + start_time + "' and '" + end_time + "' " \
"and a_dy != 0 " \
"order by 1" # 查询数据
print("\n正在获取", str(name), "的数据...")
data = pd.read_sql(sql, con=connection_1) # 读出SQL数据
start = data.time.min() # 找到最小时间
print("\n数据获取成功！")
return data, start
def mysql_conn(username,phone,email,mess):
# print(username, "\n", phone, "\n", email, "\n", mess, "\n")
#连接数据库
conn=Connection(host='localhost',user='******',password='******',port=3306,database='ly')
cursor=conn.cursor()
# ------------
data = {
'username': username,
'phone': phone,
'email': email,
'mess':mess
}
table = 'ly.talk'
# 获取到一个以键且为逗号分隔的字符串，返回一个字符串
keys = ', '.join(data.keys())
print("keys\n")
print(keys)
values = ', '.join(['%s'] * len(data))
print("values\n ")
print(values)
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
# 这里的第二个参数传入的要是一个元组
# 执行
if cursor.execute(sql, tuple(data.values())):
print('Successful')
# 提交
conn.commit()
except:
print('Failed')
conn.rollback()
# ------------
cursor.close()
conn.close()
return render_template("auth/index.html")
def get_conn(self): # 创建获取连接方法
self.conn = Connection(host=self.host,
port=self.port,
user=self.user,
password=self.password,
database=self.database,
charset=self.charset)
return self.conn # 将连接对象返回
def GenerateConn():
conn = Connection(host="localhost",
port=3306,
user='******',
passwd='123456',
db='grabpatents',
charset='utf8')
return conn
def stc():
conn = Connection(host='localhost', user='******', password='******', port=3306, database='ly')
cursor = conn.cursor()
sql = "SELECT * FROM ly.company"
cursor.execute(sql)
u = cursor.fetchall()
cursor.close()
conn.close()
return u
def __init__(self):
configEngine = ConfigEngine()
self.host = configEngine.get_param_default('dataBase', 'host')
self.port = configEngine.get_param_default('dataBase', 'port')
self.user = configEngine.get_param_default('dataBase', 'user')
self.password = configEngine.get_param_default('dataBase', 'password')
self.database = configEngine.get_param_default('dataBase', 'database')
self.charset = configEngine.get_param_default('dataBase', 'charset')
self.conn = Connection(host=self.host, port=int(self.port), user=self.user, password=self.password,database=self.database, charset=self.charset)
self.cursor = self.conn.cursor()
def __init__(self):
self.sentence_min = 4
self.sentence_max = 'POSITIVE_INFINITY'
self.num = 1000
self.mysql_conf = msg
self.conn = Connection(self.mysql_conf['host'],
self.mysql_conf['user'],
self.mysql_conf['password'],
self.mysql_conf['db'])
self.cur = self.conn.cursor()
self.sql_select = """
def generate(mysql: dict, table_name: str, model_name: str, model_path: str):
model_path = model_path % model_name
connection = Connection(**mysql)
try:
cursor = connection.cursor()
cursor.execute(
query="SELECT TABLE_COMMENT FROM information_schema.TABLES "
"WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
args=(mysql['database'], table_name))
TABLE_COMMENT, = cursor.fetchone()
cursor.close()
cursor = connection.cursor()
cursor.execute(
query=
"SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT FROM information_schema.COLUMNS "
"WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
args=(mysql['database'], table_name))
fields = cursor.fetchall()
cursor.close()
if os.path.exists(model_path):
print(f"File {model_path}:1 was overwrite.")
with open(model_path, 'w', encoding='utf8') as f:
f.write("from ._BaseModel import BaseModel\n\n\n")
f.write(f"class {model_name}(BaseModel):\n")
TABLE_COMMENT = ' '.join(TABLE_COMMENT.split())
if TABLE_COMMENT:
f.write(f' """{TABLE_COMMENT}"""\n\n')
f.write(" def __init__(self, row: dict = None):\n")
f.write(" row = dict() if row is None else row\n\n")
for COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT in fields:
if COLUMN_TYPE == 'bit(1)':
f.write(
f" self.{COLUMN_NAME} = (None"
f" if '{COLUMN_NAME}' not in row"
f" else bool(row['{COLUMN_NAME}'] == b'\\x01')"
f" if type(row['{COLUMN_NAME}']) is bytes and len(row['{COLUMN_NAME}']) == 1"
f" else row['{COLUMN_NAME}'])\n")
else:
f.write(
f" self.{COLUMN_NAME} = row['{COLUMN_NAME}'] if '{COLUMN_NAME}' in row else None\n"
)
COLUMN_COMMENT = ' '.join(COLUMN_COMMENT.split())
if COLUMN_COMMENT:
f.write(f' """{COLUMN_COMMENT}"""\n\n')
else:
f.write('\n')
except Exception as e:
connection.rollback()
print(type(e), e)
finally:
connection.close()
def connetmysql(cls, username, userpass):
try:
condb = Connection(host="localhost",
user=username,
password=userpass,
database="mysql",
charset="utf8")
cursor = condb.cursor(cursors.DictCursor)
return cursor
except OperationalError as e:
print(f"连不上mysql:{e}")
sys.exit()
except Exception as e:
print(f"连不上mysql:{e}")
sys.exit()
def test_mysql_connection(host: str, port: int, user: str, password: str,
dbname: str) -> Tuple[bool, str]:
"""
Test the database connection.
:return: status, message.
"""
try:
con = Connection(host=host,
port=port,
user=user,
password=password,
db=dbname)
con.ping()
con.close()
return True, 'success.'
except MySQLError as e:
return False, str(e).split(',')[1].strip()[1:-2]
def mysql_conn(self):
conn = Connection(host='localhost',
user='******',
password='******',
port=3306,
database='test')
cursor = conn.cursor()
a = self.lineEdit.text()
print(a)
strr = "'" + a + "'"
print(strr)
stri = 'insert into aa(name) values' + '(' + strr + ')'
print(stri)
cursor.execute(stri)
conn.commit()
print('插入成功！')
cursor.close()
conn.close()
def __init__(self):
configEngine = ConfigEngine()
file_path = os.path.dirname(
os.path.abspath(".")) + "/SeleniumForPython/config/jdbc.ini"
self.host = configEngine.get_param(file_path, 'dataBase', 'host')
self.port = configEngine.get_param(file_path, 'dataBase', 'port')
self.user = configEngine.get_param(file_path, 'dataBase', 'user')
self.password = configEngine.get_param(file_path, 'dataBase',
'password')
self.database = configEngine.get_param(file_path, 'dataBase',
'database')
self.charset = configEngine.get_param(file_path, 'dataBase', 'charset')
self.conn = Connection(host=self.host,
port=int(self.port),
user=self.user,
password=self.password,
database=self.database,
charset=self.charset)
self.cursor = self.conn.cursor()
def __conn(self):
_r = self._cfg
host = _r.get("host")
user = _r.get("user")
port = _r.get("port")
charset = _r.get("charset")
password = _r.get("password")
database = _r.get("database")
kwargs = _r.get("kwargs", {})
return Connection(host=host,
user=user,
password=password,
database=database,
port=port,
charset=charset,
cursorclass=DictCursor,
**kwargs)
def init_db():
DB_HOST = os.environ.get('MYSQL_HOST', '127.0.0.1')
DB_USER = os.environ.get('MYSQL_USER', 'root')
DB_PASS = os.environ.get('MYSQL_PASS', 'password')
DB_NAME = os.environ.get('MYSQL_DB', 'test_loic')
connection = Connection(host=DB_HOST,
user=DB_USER,
password=DB_PASS,
db=DB_NAME,
autocommit=True)
c = connection.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS user (
id int AUTO_INCREMENT,
first_name varchar(128),
last_name varchar(128),
email varchar(128),
PRIMARY KEY(id)
);
''')
return connection
def get_con(host: str = '127.0.0.1',
port: int = 3306,
user: str = 'root',
password: str = '',
dbname: str = 'moca_system'):
"""If can't get database connection return None."""
try:
con = Connection(host=host,
port=port,
user=user,
password=password,
db=dbname)
get_event_loop().run_until_complete(
core.logger.save('get a connection of mysql success',
core.logger.DEBUG))
return con
except MySQLError as error:
get_event_loop().run_until_complete(
core.logger.save(
'get a connection of mysql failed.'
'Please check your user name, password and your database status.'
f'<MySQLError: {error}>', core.logger.ERROR, True))
return None
from pymysql import Connection
from dataclasses import dataclass, field
from typing import Any
def abrir_conexion():
credenciales = {
"host": os.environ['MYSQL_HOST']
"user": os.environ['MYSQL_USER'],
"database": os.environ['MYSQL_DATABASE'],
"password": os.environ['MYSQL_PASSWORD'],
"port": 3307,
}
conexion = Connection(**credenciales)
return conexion
@dataclass
class ConexionSQL
conexion: Any = field(default_factory=abrir_conexion)
def ejecutar_query(self, query, commit=False, respuesta=True, valores=None):
cursor = self.conexion.cursor()
if valores:
# -*- coding:utf-8 -*-
"""
1.数据库连接方式
Django：使用ORM（框架）：本质是调用pymysql，MySQLdb
flask/其它：
使用原生sql，pymysql，MySQLdb（只支持python2）
或者SQLAchemy（ORM框架，与Django不同）：本质是调用pymysql，MySQLdb
2.使用pymysql模块
3.多线程共用Connection对象实现
a.加锁线程锁，threading.lock()
b.使用连接池实现
"""
from pymysql import Connection
import threading
lock = threading.Lock()
con = Connection(host='127.0.0.1', user='******', password="******",
database='test', port=3306,
charset='utf8')
cur = con.cursor()
lock.acquire()
print(cur)
lock.release()
cur.close()
con.close()
# -*- coding: utf-8 -*-
from pymysql import Connection
import time
HOST = 'localhost'
USER = '******'
PASSWORD = '******'
DATABASE = 'sinaweibo'
PORT = 3306
CHARSET = 'utf8mb4'
connection = Connection(host=HOST,
user=USER,
password=PASSWORD,
database=DATABASE,
port=PORT,
charset=CHARSET)
cursor = connection.cursor()
sql = 'select * from rural_water_quality'
cursor.execute(sql)
# TODO fetch all when deploy
result = cursor.fetchall()
print len(result)
neat_list = []
def contains(src_list, target):
for ele in src_list:
if ele[2] == target[2]:
def __init__(self):
self.conn = Connection()
# -- Imports --------------------------------------------------------------------------
from pymysql import Connection, MySQLError
from redis import Redis, RedisError
from .core import DB_CONFIG
from .. import moca_modules as mzk
# -------------------------------------------------------------------------- Imports --
# -- DB --------------------------------------------------------------------------
try:
mysql = Connection(host=DB_CONFIG['mysql']['host'],
port=int(DB_CONFIG['mysql']['port']),
user=DB_CONFIG['mysql']['user'],
password=DB_CONFIG['mysql']['password'],
database=DB_CONFIG['mysql']['database'])
cursor = mysql.cursor()
except KeyError as e:
mzk.print_error(f'Mysql database configuration error. missing key: {e}')
mzk.sys_exit(1)
except MySQLError as e:
mzk.print_error(
"Can't connect to MySQL database, Please check your database configuration."
)
mzk.print_error("And make sure your database is online.")
mzk.print_error(
"You can use 'python3 moca.py test-mysql-con' to check your database.")
mzk.print_error(f"<MySQLError: {e}>")
mzk.sys_exit(1)
file = open("472.txt","r",errors='ignore',encoding="utf8") #设置文件对象
try:
while True:
text_line = file.readlines()
if text_line:
print(text_line)
b=0
while b<200:
print("dddd")
b = b + 1
conn = Connection(host='localhost', user='******', password='******', port=3306, database='test')
cursor = conn.cursor()
a = text_line[b]
print(len(text_line))
strr = "'" + a + "'"
print(strr)
stri = 'insert into aa(inerpret) values' + '(' + strr + ')'
print(stri)
cursor.execute(stri)
conn.commit()
print('插入成功！')
cursor.close()
conn.close()
else:
