python3入门代码-Python3入门（十三）——连接数据库

#coding=utf-8

importpymysqlimportxlwtimportconfigparser#获取数据库连接

defconn(sql, hostname, username, password, database):#打开数据库连接

db = pymysql.connect(hostname, username, password, database, charset='utf8')#使用 cursor() 方法创建一个游标对象 cursor

cursor =db.cursor()#使用 execute() 方法执行 SQL 查询

cursor.execute(sql)#使用 fetchall() 方法获取所有数据.

results =cursor.fetchall()#关闭数据库连接

db.close()returnresultsdefwrite_excel(f, data, row):#创建excel对象

book =xlwt.Workbook()#添加一个表

sheet = book.add_sheet('表结构清单')#行计数

c =0#取出data中的每一个元组存到表格的每一行

for d indata:#将每一个元组中的每一个单元存到每一列

for index inrange(len(d)):#写表头

if c ==0:

sheet.write(c, index, row[index])continuesheet.write(c, index, d[index])

c+= 1

#保存excel

book.save(f)if __name__ == "__main__":

config=configparser.ConfigParser()

config.read("config.ini")

host= config.get("mysql", "host")

user= config.get("mysql", "user")

pwd= config.get("mysql", "password")

db= config.get("mysql", "database")

file= config.get("file", "file_name")

s= """SELECT

a.Table_name AS "表名",

a.Table_comment AS "表注释",

b.Ordinal_position AS "序号",

b.Column_name AS "列名",

b.Data_type AS "列类型",

b.Column_comment AS "列注释"

FROM

information_schema.TABLES a

JOIN information_schema.COLUMNS b ON a.Table_name = b.Table_name

WHERE

a.TABLE_SCHEMA = '{}'

AND a.Table_type = 'BASE TABLE'""".format(db)

row_name= ("表名", "表注释", "序号", "列名", "列类型", "列注释")#获取表结构数据

table_schema =conn(s, host, user, pwd, db)

write_excel(file, table_schema, row_name)print("success!")