python-连接数据库

 

 1.连接数据库

import pymysql
import xlsxwriter

connect = pymysql.Connect(host='localhost', port=3306, user='root', passwd='123456', db='luck', charset='utf8')
cursor = connect.cursor()
cursor.execute('select user_id,name, email, phone from users')
# 查询所有数据
fetchall = cursor.fetchall()
print('all:', fetchall)

2.查询数据导出到xlsx表格文件 

import pymysql
import xlsxwriter

connect = pymysql.Connect(host='47.98.52.53', port=3306, user='root', passwd='123456', db='luck', charset='utf8')
cursor = connect.cursor()
cursor.execute('select user_id,name, email, phone from users')
# 查询所有数据
fetchall = cursor.fetchall()
# 表格文件名称
workbook = xlsxwriter.Workbook('db-data.xlsx')
worksheet = workbook.add_worksheet()
# 行数
line = 0
for data in fetchall:
    # 列数
    row = 0
    for value in data:
        # 写入表格数据
        worksheet.write(line, row, value)
        row += 1
    line += 1
workbook.close()
print('end')