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 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 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 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 _get_cursor(conn: pymysql.Connection):
"""
:param conn:
:return:
"""
return conn.cursor()
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 _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 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 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 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
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 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 _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 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 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 _kv_shard_insert_edge(
shard_conn: pymysql.Connection, edge_id: int, from_id: UUID, to_id: UUID, data: KvetchData
) -> None:
now = datetime.now()
sql = 'INSERT into kvetch_edges (edge_id, from_id, to_id, body, created, updated) '
sql += 'VALUES(%s, %s, %s, %s, %s, %s)'
values = (edge_id, from_id.bytes, to_id.bytes, data_to_body(data), now, now)
with shard_conn.cursor() as cursor:
cursor.execute(sql, values)
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 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 _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 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 check_exist_and_create_table(conn: Connection, table_name: str,
column: Dict[str, str]) -> bool:
query = "show tables like '{}'".format(table_name)
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchone()
if not result:
create_table(conn, table_name, column)
return True
return False
def insert(con: pymysql.Connection, data: list):
cur = con.cursor()
try:
for u in data:
cur.execute('insert into user value (%s, %s, "%s")' % u)
assert 1 == cur.rowcount, 'insert error'
except Exception as e:
print('insert error: ', e)
finally:
cur.close()
def create(con: pymysql.Connection):
cur = con.cursor()
cur.execute('drop table if exists user')
cur.execute('''create table user (
id int primary key,
age int,
name varchar(20),
key(age)
) engine = example''')
cur.close()
def insert_in_table(
self,
name: str,
count: Union[int, str],
price: Union[int, str],
connection: pymysql.Connection = None,
):
if not connection:
connection = self.connection
connection.select_db("test_task")
# TODO: добавить обработку sql-инъекции
connection.cursor().execute(
"""
INSERT INTO `pricelist` \
(`name`, `count`, `price`) VALUES (%s, %s, %s);
""",
(name, count, price),
)
connection.commit()
def _kv_shard_insert_object(
shard_conn: pymysql.Connection, new_id: UUID, type_id: int, data: KvetchData
) -> UUID:
with shard_conn.cursor() as cursor:
now = datetime.now()
sql = (
'INSERT INTO kvetch_objects(obj_id, type_id, created, updated, body) ' +
'VALUES (%s, %s, %s, %s, %s)'
)
cursor.execute(sql, (new_id.bytes, type_id, now, now, data_to_body(data)))
return new_id
def _kv_shard_get_index_entries(
shard_conn: pymysql.Connection, index_name: str, index_column: str, index_value: Any
) -> List[IndexEntry]:
sql = 'SELECT target_id FROM %s WHERE %s = ' % (index_name, index_column)
sql += '%s'
sql += ' ORDER BY target_id'
rows = [] # type: List[Dict]
with shard_conn.cursor() as cursor:
cursor.execute(sql, (_to_sql_value(index_value)))
rows = cursor.fetchall()
return [IndexEntry(target_id=UUID(bytes=row['target_id'])) for row in rows]
def _kv_shard_insert_index_entry(
shard_conn: pymysql.Connection,
index_name: str,
index_column: str,
index_value: str,
target_id: UUID
) -> None:
sql = 'INSERT INTO %s (%s, target_id, created)' % (index_name, index_column)
sql += ' VALUES(%s, %s, %s)'
values = [index_value, target_id, datetime.now()]
with shard_conn.cursor() as cursor:
cursor.execute(sql, tuple(_to_sql_value(v) for v in values))
def load_vlan_by_core_id(core_id: int, db_connection: pymysql.Connection):
cursor = db_connection.cursor()
# core_id 11 is MDC core from GaaS v1 DB
query = r'SELECT number, name, core_id, uuid FROM vlans WHERE core_id=%s'
values = core_id
cursor.execute(query, values)
db_data = cursor.fetchall()
cursor.close()
vlans = convert_db_data_to_vlans(db_data=db_data)
return vlans
def _kv_shard_get_objects(shard_conn: pymysql.Connection,
obj_ids: List[UUID]) -> Dict[UUID, KvetchData]:
values_sql = ', '.join(['%s' for x in range(0, len(obj_ids))])
sql = 'SELECT obj_id, type_id, body FROM kvetch_objects WHERE obj_id in (' + values_sql + ')'
with shard_conn.cursor() as cursor:
cursor.execute(sql, [obj_id.bytes for obj_id in obj_ids])
rows = cursor.fetchall()
out_dict = OrderedDict.fromkeys(obj_ids, None) # type: Dict[UUID, KvetchData]
for row in rows:
obj_id = UUID(bytes=row['obj_id'])
out_dict[obj_id] = row_to_obj(row)
return out_dict
def query(con: pymysql.Connection, data: list):
data.sort()
cur = con.cursor()
try:
cur.execute('select * from user')
rows = cur.fetchall()
rows = [i for i in rows]
# rows.sort()
print("query rows len: %d" % len(rows))
assert rows == data, 'query error: the result of query is not equal to the data inserted'
except Exception as e:
print('query error: ', e)
finally:
cur.close()
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 _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
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)
self.url = URL
