Python/Mysql - Exporting CSV files

Asked

Viewed 257 times

0

Hello, I am developing an application to extract files from my database, in csv. The file is normally generated with the data that is in the database, but if I insert more data into the database, it generates the same file, only with what it had before. Follow the code below:

import pymysql
from pymysql import InternalError
from pathlib import Path
from tkinter import *

tess = Tk()
tess.title("tess")


def exp_rel_emp():

    conn = pymysql.connect(host="localhost", port=3306, user="root", password="", db="omnia")
    print("connect sucessfull!")

    try:
        with conn:
            conn.commit()
            statm = "SELECT * FROM omniacademp INTO OUTFILE '/TEMP/CadastroEmpresas.CSV' FIELDS TERMINATED BY ';' ENCLOSED BY ''"
            cursor = conn.cursor()
            with cursor:
                cursor.execute(statm)
                results = cursor.fetchall()
                print(results)
    except InternalError:
        Path('/TEMP/CadastroEmpresas.CSV').touch()
    finally:
        conn.close()
        print("MySQL connection is closed")


bt = Button(tess, text="tess", command=exp_rel_emp)
bt.place(x=10, y=10)


tess.mainloop()
mysql python csv

  • And if you delete the file manually and run SQL, what happens?

  • It generates a new updated, but would like it to overwrite the previous, updated.

  • Just delete the file first. As per mysql documentation, the instruction SELECT ... INTO OUTFILE will not overwrite the file for security reasons.

1 answer

0

See to mysql documentation:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT Writes the Selected Rows to a file. The file is created on the server host, so you must have the FILE Privilege to use this syntax. file_name cannot be an existing file, which Among other Things prevents files such as /etc/passwd and database Tables from being modified.

The file that will be generated cannot exist due, among other reasons, security issues, not to run the risk of overwriting fundamental system files.

That is, if the file exists, simply remove it before performing the query. Using the library pathlib you can use the method pathlib.Path.unlink.

Browser other questions tagged mysql python csv

You are not signed in. Login or sign up in order to post.