python导入和导出数据库中的数据

梦里梦外; 2022-11-19 01:23 213阅读 0赞

### 文章目录 ###

*   *  一、服务器打开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')

![免费技术交流][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3ODAyODk1_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE3ODAyODk1_size_16_color_FFFFFF_t_70]: /images/20221022/07398fa0fd0440988439839a5caaa436.png