def get_userlist():
# connc = Connection(host="39.100.228.129", port=4000, database="stay", user="******",
# password="******", charset="utf8")
connc = Connection(host="39.100.228.129",
port=4000,
database="stay",
user="******",
password="******",
charset="utf8")
cursor = connc.cursor()
# 1100
q_sql = "select * from stay.app_user limit 1100,100"
cursor.execute(q_sql)
rowcount = cursor.rowcount
print(rowcount)
# 获取所有查询内容
rows = cursor.fetchall()
# print(rows)
for row in rows:
# print("-" * 20)
# print("id:", row[0])
userids.append(row[0])
cursor.close()
connc.close()
def job1(pages):
db = Connection(host="localhost",
user="******",
password="******",
port=3306,
database='world',
charset='gbk')
cur = db.cursor()
for i in range(pages[0], pages[1]):
url1 = "http://news.xmu.edu.cn/1552/list" + str(i) + ".htm"
html = crawl(url1)
urls = parse(html)
for url in urls:
html = urlopen(url).read().decode('utf-8')
soup = BeautifulSoup(html, 'html.parser')
title = soup.find('span', {"class": 'Article_Title'})
title = title.get_text()
readnum = soup.find('span', {"class": 'WP_VisitCount'},
{"style": 'display'})
readnum = readnum.get_text()
date = soup.find('span', {"class": 'Article_PublishDate'})
date = date.get_text()
print("url=" + url)
insert_xmunews = 'insert into xmunews3(title,date1,url,views) values(%s,%s,%s,%s);'
try:
cur.execute(insert_xmunews, [title, date, url, readnum])
except Exception as e:
print("！！！！！！！！！！异常是%s" % e)
print("题目：" + title)
print("浏览次数：" + readnum)
print("发布日期：" + date)
db.commit()
cur.close()
db.close()
def get_doris_cur(conn):
c = Connection(host=conn['host'],
port=conn['port'],
user=conn['username'],
passwd=conn['password'],
use_unicode=True,
charset='utf8')
c.autocommit(True)
cur = c.cursor()
yield cur
cur.close()
c.close()
def context(conn: Connection = None) -> Generator[Cursor, None, None]:
"""
Run multiple MySQL commands in a single connection:
>>> with context() as conn, cursor:
... create_account(cursor, owner)
... create_database(cursor, owner)
"""
conn = conn or connect()
try:
yield conn.cursor()
finally:
conn.close()
def get_token(phone):
connc = Connection(host=DButils.idc_host,
port=3306,
database="walkman",
user=DButils.idc_user,
password=DButils.idc_password,
charset="utf8")
cursor = connc.cursor()
# 1100
q_sql = "select uid from walkman.user_info where phone=" + phone + " limit 1"
cursor.execute(q_sql)
rowcount = cursor.rowcount
print(rowcount)
# 获取所有查询内容
rows1 = cursor.fetchall()
print(rows1)
uid = ''
for row in rows1:
print("-" * 20)
uid = str(row[0])
print("usderid:", uid)
q_sql1 = "select token from walkman.user_login where uid=" + uid
cursor.execute(q_sql1)
rows2 = cursor.fetchall()
print(rows2)
token = ""
for row in rows2:
print("-" * 20)
token = row[0]
print("token:", token)
# userids.append(row[0])
cursor.close()
connc.close()
return token
def connect(self,
*,
read_only: bool = False,
charset: str = "utf8mb4",
read_default_file: Optional[str] = "",
read_default_group: Optional[str] = None,
ssl: Optional[Dict] = None,
**kwargs: Any) -> Generator:
"""Context-manager for a mysql connection to a remote host.
Caution:
Read-only support is limited to DML sql operations.
Important:
- This does not commit changes, that is the caller's responsibility.
- The caller should also take care of rolling back transactions on error
as appropriate.
Arguments:
read_only (bool, optional): True if this connection should use read-only
transactions. **Note**: This parameter has no effect if DRY-RUN is set.
charset (str, optional): Query charset to use.
read_default_file (str, optional): ``my.cnf``-format file to read from. Defaults
to ``~/.my.cnf``. Set to :py:data:`None` to disable.
read_default_group: Section of read_default_file to use. If not specified, it
will be set based on the target hostname.
ssl (dict, optional): SSL configuration to use. Defaults to using the
puppet CA. Set to ``{}`` to disable.
**kwargs: Options passed directly to :py:class:`pymysql.connections.Connection`.
Yields:
:py:class:`pymysql.connections.Connection`: a context-managed mysql connection.
"""
# FIXME(kormat): read-only support is limited to DML sql statements.
# https://phabricator.wikimedia.org/T254756 is needed to do this better.
read_only = read_only or self._dry_run
if read_default_file == "":
read_default_file = str(Path("~/.my.cnf").expanduser())
if read_default_file and not read_default_group:
read_default_group = "client"
if kwargs.get("host", "").startswith("labsdb"):
read_default_group += "labsdb"
if ssl is None:
ssl = {"ca": PUPPET_CA_PATH}
conn = Connection(
charset=charset,
read_default_file=read_default_file,
read_default_group=read_default_group,
ssl=ssl,
**kwargs,
)
if read_only:
conn.query("SET SESSION TRANSACTION READ ONLY")
try:
yield conn
# Not catching exceptions and rolling back, as that restricts the client code
# in how it does error handling.
finally:
conn.close()
from pymysql.connections import Connection
import time
conn = Connection(host='192.168.84.128',
passwd='mysql',
charset='utf8',
user='******',
db='tt',
autocommit=True)
conn.connect()
while 1:
cs = conn.cursor()
row = cs.execute('select * from students where id = %s ', [18])
print("row", row)
# conn.commit()
time.sleep(2)
data = cs.fetchone() # 每次取1条数据
print(data)
cs.close()
conn.close()
from pymysql.connections import Connection
connect = Connection(host="127.0.0.1",
user="******",
password="******",
database="books",
charset="utf8",
autocommit=True,
port=3306)
cursor = connect.cursor()
update_data = """UPDATE t_book set `read` = `read`+30 WHERE id = 4"""
cursor.execute(update_data)
cursor.close()
connect.close()
class MysqlDB(object):
def __init__(self):
self.conn = Connection(host, username, password, database)
self.cursor = self.conn.cursor()
def show_version(self):
sql = 'select version()'
self.cursor.execute(sql)
# return a tuple
data = self.cursor.fetchone()
print('mysql version is {}'.format(data))
def create_database(self):
try:
self.cursor.execute('create database %s charset utf8' % DB_NAME)
except pymysql.err.MySQLError as e:
print('failed creating database :{}'.format(str(e)))
exit(1)
def create_table(self):
try:
self.cursor.execute('USE {}'.format(DB_NAME))
except pymysql.err.DatabaseError as err:
print('use database {}'.format(str(err)))
exit(1)
try:
for table_name in TABLES:
table_des = TABLES[table_name]
print('creating table: {}'.format(table_name))
self.cursor.execute(table_des)
except pymysql.err.MySQLError as e:
print('crating table error {}'.format(str(e)))
exit(1)
else:
print('OK')
def insert(self):
tomorrow = datetime.now().date() + timedelta(days=1)
add_employee = '''insert into employees (first_name, last_name, hire_date, gender, birth_date)
values (%s, %s, %s, %s, %s)'''
add_salary = '''insert into salaries values (%(emp_no)s, %(salary)s,%(from_date)s,%(to_date)s)'''
data_employee = ('jack', 'ross', tomorrow, 'M', date(1977, 6, 16))
# paramtrerize
self.cursor.execute(add_employee, data_employee)
emp_no = self.cursor.lastrowid
print('the new emp_no is {}'.format(emp_no))
data_salary = {
'emp_no': emp_no,
'salary': 50000,
'from_date': tomorrow,
'to_date': date(9999, 10, 1)
}
a = self.cursor.execute(add_salary, data_salary)
self.conn.commit()
print('execute return {}'.format(a))
def delete(self):
sql = '''delete from employees where emp_no > 100 '''
effected = self.cursor.execute(sql)
self.conn.commit()
print("effected row {}".format(effected))
def update(self):
sql = '''update employees set first_name = "jack&rose" where emp_no=500011 '''
try:
self.cursor.execute(sql)
self.conn.commit()
except pymysql.MySQLError as e:
print('failed update ')
def retrieve(self):
sql = '''select first_name, last_name, hire_date from employees where hire_date between %s and %s
order by hire_date asc'''
hire_date = date(1999, 1, 1)
end_date = date(1999, 1, 3)
self.cursor.execute(sql, (hire_date, end_date))
for (first_name, last_name, hire_date) in self.cursor:
print('{}, {} was hired on {:%d-%b-%Y}'.format(
last_name, first_name, hire_date))
def __del__(self):
self.cursor.close()
self.conn.close()
