xdc_git_python_20210511/python_base/day9/连接mysql.py

# -*- coding: UTF-8 -*-

##============  ============###

import pymysql

conn = pymysql.connect(
    host='a.xdc.ink',
    port=3306,
    user='xdc',
    passwd='Xdc123@a',
    db='cmd',
    charset='utf8mb4'
)

# cursor = conn.cursor()
# cursor.execute('show databases;')
# alld = cursor.fetchall()
# print(alld)
# cursor.close()
# conn.close()

# 获取游标对象
cursor = conn.cursor()

###============ 创建数据库 ============###
# 定义sql语句
# create_tabel_sql = """create table t1
# (id int auto_increment primary key,
# name varchar(10) not null,
# age int not null
# )"""
# row = cursor.execute(create_tabel_sql)
# print(row)
# conn.commit() # 修改数据库内容要提交
# cursor.close()
# conn.close()

##============ 插入单条数据 ============###

# insert_data_sql = "insert into t1(name, age) value(%s, %s);"  # pymsql 的字符串格式化
# row = cursor.execute(insert_data_sql,('xdc', 18)) # 传入数据，使用 ， 
# print(row)

# conn.commit()
# cursor.close()
# conn.close()

##============ 插入多条数据 ============###
insert_data_sql = "insert into t1(name, age) value(%s, %s);"  # pymsql 的字符串格式化
row = cursor.executemany(insert_data_sql,[('xdc', 18),('xdc1',19)]) # 传入数据，使用 ， 
print(row)

conn.commit()
cursor.close()
conn.close()

##============ 查询数据 ============###
# # 查询表
# select_sql = "show tables;"
# cursor.execute(select_sql)

# # 打印出查询到的结果
# print(cursor.fetchall())

# cursor.close()
# conn.close()


class GetData(object):
    def __init__(self):
        self.conn = ''
        self.host = '127.0.0.1'
        self.port = 3306
        self.user = 'root'
        self.passwd = '123456'
        self.db = 'test'
        self.cnum = 5 #set retry number

    def init_connect(self):
        self.conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.db, port=self.port,
charset='utf8')

    def get_data(self):
        self.init_connect()
        cur = self.conn.cursor()
        sql = "select * from testtable"
        cur.execute(sql)
        rs = cur.fetchall()
        cur.close()
        self.conn.close()
        return rs

    def run(self):
        count = 1
        while (count <= self.cnum):
            rs = self.get_data()
            if len(rs) > 0:
                print len(rs)
                break

            print count
            sleep(10)
            count += 1

if __name__ == '__main__':
    gd = GetData()
    gd.run()