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