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