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