python導入和導出數據庫中的數據

文章目錄

• • 一、服務器打開local_infile變量權限
  • 二、客戶端加入local_infile參數
  • 三、編寫代碼

一、服務器打開local_infile變量權限

在服務器中登錄數據庫

查找變量

show global variables like 'local_infile'

修改變量

set global local_infile=on;

二、客戶端加入local_infile參數

python中

pymysql.connect(local_infile = 1) #舉例子

遠程登錄

mysql -u root -p --local-infile=1

三、編寫代碼

import pymysql
from openpyxl import Workbook
class Database:
def __init__(self,db,table):
self.db = db
self.table = table
def connection(self):
config = {
'host': '',
'port': 33025,
'user': 'root',
'passwd': '',
'charset': 'utf8',
'database':db,
'local_infile': 1
}
connection = pymysql.connect(**config)
#鏈接database
#創建可執行sql語句的游標
cursor = connection.cursor()
#查詢
sql = 'select * from {};'.format(self.table)
#執行
count = cursor.execute(sql)
#獲取全部結果
self.result = cursor.fetchall()
#獲取MySQL中的數據字段名稱
self.fields = cursor.description
self.cursor,self.connection = cursor,connection;
def close(f):
def test(self,*args,**kwargs):
f(self,*args,**kwargs)
#關閉連接
self.connection.close()
#關閉游標
self.cursor.close()
print("成功關閉連接和游標.")
return test
@close
def outfile(self):
#創建一個excel
wb = Workbook()
#創建一個sheet
sheet = wb.active
# 寫上字段信息
headers = ['A1','B1','C1','D1']
for index,i in enumerate(self.fields):
sheet[headers[index]] = i[0]
# 獲取並寫入數據段信息
for index,i in enumerate(self.result):
sheet.append(list(i))
print('正在保存第' + str(index) + '列信息')
#wb.save(f"/storage/emulated/0/{table}.xlsx")
wb.save(f"C:/Users/Administrator/Desktop/{
table}.xlsx")
@close
def infile(self,path):
''' 加載文件, 替換到xx表格, 編碼設置utf-8, 字段以逗號結尾, 換行符以\r\n結尾, 忽略第一行; '''
data_sql = "LOAD DATA LOCAL INFILE '%s' REPLACE INTO TABLE %s CHARACTER SET UTF8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (path,self.table)
self.cursor.execute(data_sql)
self.connection.commit()
print("數據提交成功！")
if __name__=="__main__":
db,table = input("請輸入數據庫名和表名:").split(',')
database = Database(db,table)
database.connection()
choice = input("導出數據文件:0,上傳數據文件:1,請輸入:")
if choice=="0":
database.outfile()
elif choice =="1":
database.infile('C:/Users/Administrator/Desktop/book.csv')