Log in to the database on the server
Find variables
show global variables like 'local_infile'
Modify variables
set global local_infile=on;
python in
pymysql.connect(local_infile = 1) # For example
Remote login
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)
# link database
# Create executable sql The cursor of the statement
cursor = connection.cursor()
# Inquire about
sql = 'select * from {};'.format(self.table)
# perform
count = cursor.execute(sql)
# Get all the results
self.result = cursor.fetchall()
# obtain MySQL Data field name in
self.fields = cursor.description
self.cursor,self.connection = cursor,connection;
def close(f):
def test(self,*args,**kwargs):
f(self,*args,**kwargs)
# Close the connection
self.connection.close()
# Close cursor
self.cursor.close()
print(" Successfully closed the connection and cursor .")
return test
@close
def outfile(self):
# 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(self.fields):
sheet[headers[index]] = i[0]
# Get and write data segment information
for index,i in enumerate(self.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}.xlsx")
@close
def infile(self,path):
''' Load the file , Replace the xx form , Encoding settings utf-8, Fields end with commas , Line break with \r\n ending , Ignore the first line ; '''
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(" Data submitted successfully ！")
if __name__=="__main__":
db,table = input(" Please enter the database name and table name :").split(',')
database = Database(db,table)
database.connection()
choice = input(" Export data file :0, Upload data files :1, Please enter :")
if choice=="0":
database.outfile()
elif choice =="1":
database.infile('C:/Users/Administrator/Desktop/book.csv')
