save content of table into file

save content of table into file - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: save content of table into file (/thread-20576.html)

Pages: 1 2

save content of table into file - atlass218 - Aug-20-2019

hi;
I want to save the content of the table of my sqlite database into file (text, or pdf , or csv)
for text file I proceed like that but it was not worked :

def createFileFromLoc35R(self):

    conn = sqlite3.connect (ils35R.db')
    curseur=conn.cursor()
# loc35R table of the ils35R database :			
    c=curseur.execute("SELECT * FROM loc35R")
				  
    listeTableLoc35R=c.fetchall()

    f=open("guru99.txt", "a+")

    for readData in listeTableLoc35R :
	    f.write(readData)
	    f.write("\n")
    f.close()

RE: save content of table into file - Gribouillis - Aug-20-2019

atlass218 Wrote:I proceed like that but it was not worked :

What happened? Did python write an error message? Did it write a file?

RE: save content of table into file - atlass218 - Aug-21-2019

Hi; I modify my function like that :

	def reateFileFromLoc35R(self):
		script_path_ils35R = os.path.dirname(os.path.realpath(__file__))#récupérer le répertoire courant
		new_path_ils35R=script_path_ils35R + '\\data_base'  #concaténer au path ci-dessus  le path du dossier data_base
		os.makedirs(new_path_ils35R, exist_ok=True)   #creer le dossier data_base au nouveau path
	
		
		conn = sqlite3.connect (new_path_ils35R+'\\ils35R.db')
		curseur=conn.cursor()			
		c=curseur.execute("SELECT * FROM loc35R")
				  
		listeTableLoc35R=c.fetchall()

		f=open("guru99.txt", "a+")

		for readData in listeTableLoc35R :
			f.write(readData[0])
			f.write("\n")
        
		f.close()
		print("ceci est l'operation de sauvegarde dans un fichier texte")

when I execute the function by clicking on the button :
the sentence : ceci est l'operation de sauvegarde dans un fichier texte
appear at the bottom of my editor but I don't find the text file at the directory that containt my index.py

RE: save content of table into file - ThomasL - Aug-21-2019

Is there a reason you open file with "a+" if you only want to write to it?
To write to the dir where the .py file was started use first line.
To handle reading/writing files use "with" command.
To create output to write to file use f-strings (available since Python 3.6)

local_dir = os.path.dirname(__file__)
with open(os.path.join(local_dir, "guru99.txt"), "w") as file:
    for data in listeTableLoc35R:
        file.write(f'{data[0]}\n')

RE: save content of table into file - atlass218 - Aug-22-2019

Hi; thank you for the answer ; I will test your code.
I made the mode "a" instead of "w" during the writing in the text file in order to save the information contained in this same file
I want all new information added to the contents of the file without overwriting the information that already exists in this file

RE: save content of table into file - atlass218 - Aug-23-2019

I use this code to create a text file and save in the database information

conn = sqlite3.connect (new_path_ils35L+'\\ils35L.db')
curseur=conn.cursor()			
c=curseur.execute("SELECT * FROM gp35L")
                        
listeTablegp35R=c.fetchall()

local_dir = os.path.dirname(__file__)
	with open(os.path.join(local_dir, "filesave/gp35L.txt"), "w",encoding="utf-8") as file_gp35L:
		for data in listeTablegp35L:
			for k in range(10) :
				file_gp35L.write(f'{data[k]}\t')
			file_gp35L.write("\n")

unfortunately the information from the database is badly arranged following the columns

[Image: lcfq.png]

I need help to resolve this problem
thanks

RE: save content of table into file - SheeppOSU - Aug-23-2019

(Aug-23-2019, 12:12 PM)atlass218 Wrote: unfortunately the information from the database is badly arranged following the columns

How should it be arranged?

RE: save content of table into file - atlass218 - Aug-23-2019

I have already managed to display the contents of the database in a pyqt5 widget table, see the image below:
[Image: qsap.jpg]

I would like in my text file the columns are arranged as on this picture

RE: save content of table into file - Gribouillis - Aug-23-2019

You can use a specialized module such as ptable

RE: save content of table into file - atlass218 - Aug-26-2019

the paragraph to import information data from a database cursor to the prettytable,there is the command :
mytable = from_cursor(cursor)
does not work and creates an error.
at one video in youtube I remark that I must write it like that : mytable = from_db_cursor(cursor)

my final works now correctely
after installing prettytable by : pip install PTable

and :

from prettytable import from_db_cursor

#Fonction pour creer un fichier texte et sauvegarder dedans le contenu de la bdd :
def create_file_from_loc35R(self):

	script_path_ils35R = os.path.dirname(os.path.realpath(__file__))#récupérer le répertoire courant
	new_path_ils35R=script_path_ils35R + '\\data_base'  #concaténer au path ci-dessus  le path du dossier data_base
	os.makedirs(new_path_ils35R, exist_ok=True)   #creer le dossier data_base au nouveau path

	script_path_filesave_loc35R = os.path.dirname(os.path.realpath(__file__))#récupérer le répertoire courant
	new_path_filesave_loc35R=script_path_filesave_loc35R + '\\filesave'  #concaténer au path ci-dessus  le path du dossier data_base
	os.makedirs(new_path_filesave_loc35R, exist_ok=True)   #creer le dossier data_base au nouveau path
        
	try:

		conn = sqlite3.connect (new_path_ils35R+'\\ils35R.db')
		curseur=conn.cursor()			
		c=curseur.execute("SELECT * FROM loc35R")
							
		mytable = from_db_cursor(c)
		table_txt = mytable.get_string()

		local_dir = os.path.dirname(__file__)
			with open(os.path.join(local_dir, "filesave/loc35R.txt"), "w",encoding="utf-8") as file_loc35R:
				file_loc35R.write(table_txt)
        
	except :
		self.messageInfo_filesave()

there is the output text file :

[Image: dt7g.jpg]

thanks for the link of the prettytable