程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式師世界 >> 編程語言 >  >> 更多編程語言 >> Python

Python exports database data in a tabular file

編輯:Python

After reading a lot of tutorials, I found that they are very cumbersome , I have always tried to be concise
It took hours , Finally, a more perfect scheme is sorted out

System :debian9

One 、 open 3306 port

vim /etc/mysql/mariadb.conf.d/50-server.cnf


Uncomment this line of code in the figure above

Two 、 To write python Code

import pymysql
from openpyxl import Workbook
def outfile(db,table):
# link database
connection = pymysql.connect(host="",user="root",password="",database=db,charset="utf8",port=3306)
# Create executable sql The cursor of the statement 
cursor = connection.cursor()
# Inquire about 
sql = 'select * from {};'.format(table)
# perform 
count = cursor.execute(sql)
# Get all the results 
result = cursor.fetchall()
#print(result[0])
# obtain MySQL Data field name in 
fields = cursor.description
# Create a excel
wb = Workbook()
# Create a sheet
sheet = wb.active
# Write the field information 
headers = ['A1','B1','C1','D1']
for index,i in enumerate(fields):
sheet[headers[index]] = i[0]
# Get and write data segment information 
for index,i in enumerate(result):
sheet.append(list(i))
print(' Saving the ' + str(index) + ' Column information ')
#wb.save(f"/storage/emulated/0/{table}.xlsx")
wb.save(f"C:/Users/Administrator/Desktop/{table}.xls")
db,table = input(" Please enter the database name and table name :").split(',')
outfile(db,table)

All kinds of pits that I met :
1. Local server mysql Of 3306 Port opening
2. Specify the configuration file port for intranet penetration 3306
3.pymysql.connect Medium password For the database password instead of root User's password
4. Because I am an intranet penetration pymysql.connect Add port Parameter refers to the public network provided for intranet penetration ip Corresponding port


  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式師世界 All Rights Reserved