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]])