def fetch_db_structure(conn: pymysql.Connection, db_name: str) -> dict:
""""""
""""""
conn.select_db(db_name)
cur = conn.cursor()
cur.execute("SHOW TABLES")
tables = []
for table in cur.fetchall():
tables.append(table[0])
desc = {}
for table in tables:
table_desc = {}
cur.execute("SHOW FULL COLUMNS FROM " + str(table))
for row in cur.fetchall():
row_desc = {
str(row[0]): {
'type': str(row[1]),
'collation': str(row[2]),
'null': str(row[3]),
'default': str(row[4]),
'extra': str(row[5]),
'privileges': str(row[6]),
'comment': str(row[7]),
}
}
table_desc.update(row_desc)
desc.update({table: table_desc})
return desc
def excute_sql(connection:pymysql.Connection,sql_str:str,args=(),is_return=False):
"""执行sql语句
Arguments:
connection {pymysql.Connection} -- mysql connection
sql_str {str} -- 执行的语句
Keyword Arguments:
args {tuple} -- 执行的语句需要的参数
is_return {bool} -- 当前执行的语句是否会返回值 (default: {False})
Raises:
AttributeError: args必须要为元组
Returns:
[type] -- [description]
"""
if not isinstance(args,tuple):
raise AttributeError('args必须为元组')
with connection.cursor() as cursor:
if len(args) != 0:
cursor.execute(sql_str, args)
else:
cursor.execute(sql_str)
if is_return:
result = cursor.fetchone()
return result
else:
connection.commit()
return None
class DBUtil:
# 初始化方法，实例对象需要传入的参数，有域名，端口，用户名，密码，数据库，字符集
def __init__(self, host, port, user, password, database, charset):
self.host = host
self.port = port
self.user = user
self.password = password
self.database = database
self.charset = charset
self.conn = None
self.cur = None
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 get_cur(self): # 创建获取游标方法
self.cur = self.get_conn().cursor()
return self.cur # 将游标对象返回
def close_scr(self):
if self.cur: # 如果游标是空就不执行
self.cur.close() # 非空就关闭
self.cur = None # 并重置属性为None
if self.conn:
self.conn.close()
self.conn = None
def initialize_table(connection: pymysql.Connection,
table_name: str,
recreate: bool = False):
"""
Initializes the remote table.
When `recreate` parameter is True, it drops the table if it exists.
:param connection: pymysql.Connection - connection to the database
:param table_name: str - name of ther table
:param recreate: bool - whether to drop the table
:return:
"""
with connection.cursor() as cursor:
if recreate:
cursor.execute(f"""
DROP TABLE IF EXISTS `{table_name}`
""")
connection.commit()
cursor.execute(f"""
CREATE TABLE IF NOT EXISTS `{table_name}` (
`airline_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`alias` varchar(30),
`iata` varchar(3) NOT NULL,
`icao` varchar(10) NOT NULL,
`callsign` varchar(50),
`country` varchar(50) NOT NULL,
`active` varchar(1) NOT NULL
)
""")
connection.commit()
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 update_vlan(file_vlan: Vlan, db_vlan: Vlan,
db_connection: pymysql.Connection):
"""Update the vlan"""
cursor = db_connection.cursor()
query = r'UPDATE vlans SET name=%s, description=%s WHERE uuid=%s'
values = (file_vlan.name, file_vlan.description, db_vlan.uuid)
cursor.execute(query, values)
db_connection.commit()
logging.info('vlan #%s was updated' % db_vlan.number)
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 insert_tsvdata(conn: Connection, data_dir: str, file_str: str, lang: str):
# check authors table exist
check_exist_and_create_table(
conn, 'authors',
OrderedDict([('id', 'int(5) NOT NULL AUTO_INCREMENT'),
('name', 'varchar(50) NOT NULL UNIQUE'),
('PRIMARY KEY', '(id, name)')]))
data_dir = data_dir + '/' if data_dir[-1] != '/' else data_dir
files = glob.glob('{}{}*'.format(data_dir, file_str))
prepare_table(file_str)
author_rule = re.compile(r'(\(.+\))|♠')
for fpath in files:
year = int(fpath.replace('.tsv', '')[-4:])
with open(fpath, 'r') as f:
content = f.read()
content = content.replace('\0', '')
tsv = csv.DictReader(StringIO(content), delimiter='\t')
rows = [row for row in tsv]
paper_authors = [[
author_rule.sub('', author).replace('\b', '')
for author in row['authors'].split(',')
] for row in rows]
# insert author names
authors = list(
set([
author for paper_author in paper_authors
for author in paper_author
]))
query = "INSERT IGNORE INTO authors VALUES(0, %s)"
cursor = conn.cursor()
cursor.executemany(query, authors)
conn.commit()
# insert paper informations
query = "INSERT IGNORE INTO papers\
(id, year, label, task,\
session, title, url, introduction, conference, lang) \
VALUES (0, {0}, %s, %s, %s, %s, %s, %s, '{1}', '{2}')\
".format(year, file_str.upper(), lang)
data = [[
row['class'], row['task'], row['session'], row['title'],
row['url'], row['introduction']
] for row in rows]
cursor.executemany(query, data)
conn.commit()
# insert information of authors writing papers
query = "INSERT IGNORE INTO paper_written_author(author_id, paper_id)\
SELECT authors.id, papers.id\
from authors, papers\
where authors.name = %s and papers.title = %s"
for author, insert_data in zip(paper_authors, data):
for name in author:
cursor.execute(query, [name, insert_data[3]])
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 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 __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 create_table(self, connection: pymysql.Connection = None):
if not connection:
connection = self.connection
connection.select_db("test_task")
connection.cursor().execute("""
CREATE TABLE IF NOT EXISTS `pricelist`\
(`name` varchar(20),\
`count` varchar(20),\
`price` varchar(20),\
`action` varchar(20))\
""")
def search_function_from_wikipedia_database(
token: str,
wikipedia_db_connector: Connection,
page_table_name: str = 'page',
page_table_redirect: str = 'redirect') -> List[str]:
"""*
部分文字検索をするときに使う
"""
def decode_string(string):
try:
unicode_string = string.decode('utf-8')
return unicode_string
except:
return None
# It searches article name with exact same name as token
cursor = wikipedia_db_connector.cursor() # type: cursors
page_query = """SELECT page_id, page_title, page_is_redirect FROM {} WHERE (page_title = %s OR page_title LIKE %s) AND page_namespace = 0""".format(
page_table_name)
cursor.execute(page_query, (token, '{}\_(%)'.format(token)))
fetched_records = list(cursor.fetchall())
page_names = [
page_id_title[1] for page_id_title in fetched_records
if page_id_title[2] == 0
]
redirect_names = [
page_id_title[0] for page_id_title in fetched_records
if page_id_title[2] == 1
]
cursor.close()
if not redirect_names == []:
cursor = wikipedia_db_connector.cursor() # type: cursors
select_query = """SELECT rd_title FROM {} WHERE rd_from IN %s""".format(
page_table_redirect)
cursor.execute(select_query, (redirect_names, ))
article_page_names = [
page_id_title[0] for page_id_title in cursor.fetchall()
]
cursor.close()
else:
article_page_names = []
article_name_string = list(
set([
decode_string(article_name)
for article_name in page_names + article_page_names
if not decode_string(article_name) is None
]))
return article_name_string
def stream_to_db(conn: pymysql.Connection, stream_reader: csv.DictReader,
table: str) -> None:
fieldname_sql = ','.join(stream_reader.fieldnames)
values_placeholder_str = ','.join(['%s'] * len(stream_reader.fieldnames))
values_generator = ([
record[fieldname] for fieldname in stream_reader.fieldnames
] for record in stream_reader)
with conn.cursor() as cur:
cur.executemany(
f'INSERT INTO {table}({fieldname_sql}) VALUES ({values_placeholder_str})',
values_generator)
conn.commit()
def insert_vlan(vlan: Vlan, db_connection: pymysql.Connection):
"""Insert the vlan into the gaas v1 DB"""
import uuid
cursor = db_connection.cursor()
query = r'INSERT INTO vlans (core_id, number, name, description, state_id, uuid) VALUES (%s,%s,%s,%s,%s,%s)'
# core_id 11 is MDC core from GaaS v1 DB, state 2 is deployed
values = (vlan.core, vlan.number, vlan.name, vlan.description, 2,
uuid.uuid4().__str__())
try:
cursor.execute(query, values)
db_connection.commit()
logging.info('vlan #%s was created' % vlan.number)
except pymysql.IntegrityError:
logging.error('vlan #%s was found duplicate' % vlan.number)
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 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 __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 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
class MysqlDb(object):
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 query_one(self,sql,params=None):
"""执行查询数据语句"""
self.cursor.execute(sql,params)
return self.cursor.fetchone()
def query_all(self,sql,params=None):
"""执行查询数据语句"""
self.cursor.execute(sql,params=None)
return self.cursor.fetchall()
def update_or_delete(self,sql, params=None):
"""执行操作数据语句"""
self.cursor.execute(sql, params=None)
def roll_back(self):
self.cursor.rollback()
def commit(self):
self.cursor.commit()
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 _get_cursor(conn: pymysql.Connection):
"""
:param conn:
:return:
"""
return conn.cursor()
def _kv_shard_get_edges(
shard_conn: pymysql.Connection, edge_id: int, from_id: UUID, after: UUID, first: int
) -> List[EdgeData]:
sql = 'SELECT from_id, to_id, created, body '
sql += 'FROM kvetch_edges WHERE edge_id = %s AND from_id = %s'
args = [edge_id, from_id.bytes]
if after:
sql += """AND row_id >
(SELECT row_id from kvetch_edges WHERE edge_id = %s
AND from_id = %s
AND to_id = %s) """
args.extend([edge_id, from_id.bytes, after.bytes])
sql += ' ORDER BY row_id'
if first:
sql += ' LIMIT %s' % first
with shard_conn.cursor() as cursor:
cursor.execute(sql, tuple(args))
rows = cursor.fetchall()
def edge_from_row(row: dict) -> EdgeData:
return EdgeData(
from_id=UUID(bytes=row['from_id']),
to_id=UUID(bytes=row['to_id']),
created=row['created'],
data=body_to_data(row['body'])
)
return [edge_from_row(row) for row in rows]
def _get_record_count(self: unittest.TestCase, conn: pymysql.Connection,
table_name: str) -> None:
with conn.cursor() as cursor:
sql = f"select count(*) from {table_name}"
cursor.execute(sql)
count = cursor.fetchone()
return count["count(*)"]
def table_contents(con: pymysql.Connection,
table: str) -> List[Dict[str, str]]:
with con.cursor() as cur:
cur.execute(f'SELECT * FROM {table}')
fieldnames = [field[0] for field in cur.description]
return [{fieldnames[i]: str(row[i])
for i in range(len(row))} for row in cur.fetchall()]
def is_table_empty(table: str, connection: Connection) -> bool:
"""Simple check to determine if the provided table is empty in the database on the other end of connection"""
field_alias = 'is_empty'
sql = f'SELECT (count(1) = 0) as `{field_alias}` FROM `{scrub_sql_name(table)}` LIMIT 1;'
with connection.cursor(cursors.DictCursor) as cursor:
cursor.execute(sql)
result = cursor.fetchone().get(field_alias, 0)
return bool(result)
def GenerateConn():
conn = Connection(host="localhost",
port=3306,
user='******',
passwd='123456',
db='grabpatents',
charset='utf8')
return conn
def execute_sql_command(connection: mariadb.Connection, statement: str):
# Warning! that is insecure and musn't be used as GUI function!
"""
executes any sql command
:param connection: Connection class on wich query will operate
:param statement: String to execute sql query
:return:
"""
isinstance(connection, mariadb.Connection.__class__)
cursor = connection.cursor()
try:
cursor.execute(statement)
connection.commit()
return cursor.fetchall()
except Exception as e:
connection.rollback()
print(e)
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 _db_execute(query: str, conn: pymysql.Connection):
"""Helper method for executing the given MySQL non-query.
:param query: MySQL query to be executed.
:param conn: MySQL connection.
"""
cursor = conn.cursor()
cursor.execute(query)
cursor.close()
def _db_table_exists(table_name: str, conn: pymysql.Connection) -> bool:
"""Helper method for checking whether the given MySQL table exists.
:param table_name: Name of the MySQL table to be checked.
:param conn: MySQL connection.
:return True iff the given MySQL table exists.
"""
cursor = conn.cursor()
cursor.execute("""
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_name = '{0}'""".format(table_name))
table_exists = cursor.fetchone()[0] == 1
cursor.close()
return table_exists
from bs4 import BeautifulSoup
import re
import thread,threading
from pymysql import Connection
URL = "http://searchxh.news.cn/was5/web/search?channelid=229767&searchword=%E5%85%89%E4%BC%8F&prepage=&page="
LOCK = thread.allocate_lock()
#mysql connection
HOST = 'localhost'
USER='******'
PASSWORD='******'
DATABASE='policyDB'
PORT= 3306
CHARSET = 'utf8mb4'
connection = Connection(host=HOST,user=USER,password=PASSWORD,database=DATABASE,port=PORT,charset=CHARSET)
cursor = connection.cursor()
#page info
num_count = 1648
numlist = [i for i in range(1,num_count/10+2)]
pagenum = 1
rec_num = 0
#print bsoup.prettify()
class MyThread(threading.Thread):
def __init__(self,name,fromIndex,toIndex):
global numlist,URL
threading.Thread.__init__(self)
'title':'',
'category':'',
'sourceType':'',
'area':'',
'startTime':'',
'endTime':'',
'indexPathName':'indexPath2'
}
HOST = 'localhost'
USER='******'
PASSWORD='******'
DATABASE='policyDB'
PORT= 3306
CHARSET = 'utf8mb4'
connection = Connection(host=HOST,user=USER,password=PASSWORD,database=DATABASE,port=PORT,charset=CHARSET)
cursor = connection.cursor()
data = urllib.urlencode(post_dict)
request = urllib2.Request(url,data,headers)
response = urllib2.urlopen(request)
bs = BeautifulSoup(response.read(),"lxml")
#print bs.prettify()
rec_count = int(bs.b.string)
rec_per_page = 20
page_count = rec_count/20 if rec_count%20==0 else rec_count/20+1
#print page_count
urls = []
names = []
