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():
# 连接数据库用
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 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 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 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 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 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 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 add_rating_to_dish(conn: pymysql.Connection, dish_id: int, user_id,
stars: int):
"""
Adds a rating to the given dish
:param conn: The pymysql connection
:param dish_id: The id of the rated dish
:param user_id: The id of the rating user
:param stars: The amount of stars the user gave the dish
"""
cur = conn.cursor()
insert_stmt = 'INSERT INTO dish_ratings (dish_id, user_id, stars) VALUES (%s, %s, %s)'
cur.execute(insert_stmt, (dish_id, user_id, stars))
conn.commit()
cur.close()
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 save_dishes_to_sql(conn: pymysql.Connection, dishes: [Dish]):
"""
Saves the given dishes to the database
:param conn: The pymysql connection
:param dishes: The dishes to be saved
"""
if len(dishes) < 1:
return
cur = conn.cursor()
dish_tuples = []
dish_availability_tuples = []
for dish in dishes:
if len([item for item in dish_tuples if item[1] == dish.name]) > 0:
continue
dish_tuples.append((dish.dish_id, dish.name, dish.comparable_name))
dish_availability_tuples.append(
(dish.dish_id, dish.date, dish.price, dish.category))
insert_dish = \
'INSERT INTO dishes (dish_id, name, comparable_name) ' \
'VALUES (%s, %s, %s) ' \
'ON DUPLICATE KEY UPDATE ' \
' dish_id = VALUES(dish_id),' \
' name = VALUES(name)'
# Duplicate key can in theory be ignored but update also works
insert_dish_availability = \
'INSERT INTO dish_availability (dish, available_date, price, category) VALUES (%s, %s, %s, %s) ' \
'ON DUPLICATE KEY UPDATE available_id = VALUES(available_id)'
dishes_success = cur.executemany(insert_dish, dish_tuples)
dish_availabilities_success = cur.executemany(insert_dish_availability,
dish_availability_tuples)
if dishes_success and dish_availabilities_success:
conn.commit()
else:
conn.rollback()
raise Exception('Error saving menu to database')
cur.close()
def insert_from_file(connection: pymysql.Connection, table_name: str,
file_path: str) -> None:
"""
Inserts a csv file into database
:param connection: pymysql.Connection - connection to the database
:param table_name: str - table name where to insert the data
:param file_path: str - full file path to the file
:return:
"""
with open(file_path, 'r') as file, connection.cursor() as cursor:
reader = csv.reader(file)
for line in reader:
cursor.execute(
f"""
REPLACE INTO {table_name} (airline_id, name, alias, iata, icao, callsign, country, active)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", tuple(line))
connection.commit()
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()
for record in result:
if len(neat_list) == 0:
neat_list.append(record)
else:
print "neat record list length is: " + str(len(neat_list))
if not contains(neat_list, record):
neat_list.append(record)
time.sleep(0.005)
print "clear duplicated record complete! new clean table contains " + str(
len(neat_list)) + " rows..."
time.sleep(1)
i = 1
for record in neat_list:
# TODO replace test to new table
# further clean record to remove :
from_user = record[1]
post_content = record[2]
post_date = record[3]
new_content = post_content.lstrip(":")
new_record = (from_user, new_content, post_date)
sql = 'insert into neat_rural_water_quality(from_user, post_content, post_date) VALUES(%s, %s, %s)'
cursor.execute(sql, new_record)
connection.commit()
print str(i) + " record inserted! "
i += 1
time.sleep(0.003)
# TODO close Connection
connection.close()
'fromwhere':'chinalaw_article',
'key':'新能源',
'title':'',
'category':'',
'sourceType':'',
'area':'',
'startTime':'',
'endTime':'',
'indexPathName':'indexPath2'
}
data = urllib.urlencode(post_dict)
request = urllib2.Request(url,data,headers)
response = urllib2.urlopen(request)
bs = BeautifulSoup(response.read())
result_set = bs.find_all(href=re.compile('^http://fgk.chinalaw.gov.cn/'))
for a in result_set:
url = a['href']
strings = a.strings
name = ""
for string in strings:
name +=string
rec = (rec_id,url,name.strip())
try:
sql = "insert into URL values(%s,%s,%s)"
cursor.execute(sql,rec)
connection.commit()
finally:
print "SUCCESS"
connection.close()
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:
break
finally:
file.close()
def insert_tsvdata(conn: Connection, data_dir: str, file_str: str, lang: str):
# check authors table exist
data_dir = data_dir + "/" if data_dir[-1] != "/" else data_dir
files = glob.glob("{}{}*".format(data_dir, file_str))
author_rule = re.compile(r"(\(.+\))|♠")
cursor = conn.cursor()
for fpath in files:
year = int(fpath.replace(".tsv", "")[-4:])
print(year)
query = "INSERT IGNORE INTO year VALUES(0, %s)"
cursor.execute(query, year)
conn.commit()
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.executemany(query, authors)
conn.commit()
# insert paper task
query = "INSERT IGNORE INTO task VALUES(0, %s, %s)"
tasks = list(
{row["task"]: row["class"]
for row in rows if row["task"] != ""}.items())
cursor.executemany(query, tasks)
conn.commit()
# insert paper informations
query = "INSERT IGNORE INTO papers\
(paper_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.author_id, papers.paper_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]])
