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 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 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 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 test_mysql_connection(host: str, port: int, user: str, password: str, dbname: str) -> Tuple[bool, str]: """ Test the database connection. :return: status, message. """ try: con = Connection(host=host, port=port, user=user, password=password, db=dbname) con.ping() con.close() return True, 'success.' except MySQLError as e: return False, str(e).split(',')[1].strip()[1:-2]
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()
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()
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 close_connection(self, connection: pymysql.Connection = None): if not connection: connection = self.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()
# -*- coding:utf-8 -*- """ 1.数据库连接方式 Django:使用ORM(框架):本质是调用pymysql,MySQLdb flask/其它: 使用原生sql,pymysql,MySQLdb(只支持python2) 或者SQLAchemy(ORM框架,与Django不同):本质是调用pymysql,MySQLdb 2.使用pymysql模块 3.多线程共用Connection对象实现 a.加锁线程锁,threading.lock() b.使用连接池实现 """ from pymysql import Connection import threading lock = threading.Lock() con = Connection(host='127.0.0.1', user='******', password="******", database='test', port=3306, charset='utf8') cur = con.cursor() lock.acquire() print(cur) lock.release() cur.close() con.close()