使用 python3 将数据从 sqlite3 转换为 csv、xml 时出现问题

Question

*注意 - 这是为了一个任务，而不是提供一个解决方案，如果你能指出我在哪里搞砸了我的代码，那将不胜感激。

我正在编写一个与sqlite3表，XML文件和csv文件交互的小程序。当用户第一次运行该程序时，我会检查应用程序目录中的每个文件，然后创建它们/用数据填充它们。

应该发生什么(当目录中不存在文件时(： 1. 创建表(工资率.db( 2. 在表中插入值 3. 使用表中的数据，将相同的值插入到 XML 文件中(payrate.xml( 4. 同样，使用表中的数据，将相同的值插入 CSV 文件(payrate.csv(

发生了什么事情： 1. 创建所有三个文件 2. XML 文件仅包含我的程序中指定的"根"值 3. CSV 文件完全为空 4.程序不会返回任何错误。tkinter GUI加载和显示都很好

我尝试对此进行搜索，并且看到了类似的错误，但是我找到的解决方案都没有解决我所看到的问题。如能就此问题提供任何指导，将不胜感激。

我的猜测是，在填充"行"列表之前，我的连接以某种方式关闭，这会导致空数据。我只是不确定如何解决这个问题。

#!/usr/bin/env/python3
import csv
import os
import os.path as op
import sqlite3 as sq
from contextlib import closing
import xml.etree.ElementTree as et
import tkinter as tk
from tkinter import ttk
def checkDir(self):
directory = op.dirname(op.abspath(__file__))
path = op.join(directory, 'payrate.db') # Path of payrate.db
DB_LOC = op.join(directory, 'payrate.db')
conn = sq.connect(DB_LOC) # Create connection object
c = conn.cursor() # Get a cursor object
if os.path.exists(path) == False:
# Database does not exist
self.err['text'] = "Data not found. Creating the database..."
# Payrate table string
tableString = """Create Table payrate(
ID INTEGER not null primary key,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
PAYRATE DECIMAL(99999, 2)"""
c.execute(tableString) # Create a table
## Insert rows of data into the table
c.execute("INSERT INTO PAYRATE VALUES(0,'John', 'Nolan', 99999.99)")
c.execute("INSERT INTO PAYRATE VALUES(1,'Jane', 'Doe', 99999.99)")
c.execute("INSERT INTO PAYRATE VALUES(2,'Tom', 'McCune', 99999.99)")
c.execute("INSERT INTO PAYRATE VALUES(3,'Tim', 'Guerin', 99999.99)")
c.execute("INSERT INTO PAYRATE VALUES(4,'Sally', 'Pleas', 99999.99)")
c.execute("INSERT INTO PAYRATE VALUES(5,'Miranda', 'Conrad', 99999.99)")
self.err['text'] = "Database created successfully. Checking for XML..."
# Database already existed 
else:
self.err['text'] = "Database loaded successfully. Checking for XML file..."
# set a variable with the database contents for reuse
rows = list(c.fetchall())
# change path to the xml document and check for existence
path = op.join(directory, 'payrate.xml')
if os.path.exists(path) == False:
# database created, copy data to xml
self.err['text'] = "Data not found. Creating the XML file..."
# select all data from the database
root = et.Element('Payrates')
root.set('version', '1.0')
et.ElementTree(root).write('payrate.xml')
for row in rows:
employee = et.SubElement(root, 'Employee',
{
'ID':row['ID'],
'FirstName':row['FIRST_NAME'],
'LastName':row['LAST_NAME'],
'PayRate':row['PAYRATE'],
})
et.ElementTree(employee).write('payrate.xml')
self.err['text'] = "XML file created successfully. Checking for CSV..."
# XML file already existed
else:
self.err['text'] = "XML file loaded successfully. Checking for CSV file"
# update path and check for CSV file
path = op.join(directory, 'payrate.csv')
if os.path.exists(path) == False:
# database/xml created, copy data to csv
self.err['text'] = "Data not found. Generating the CSV file..."
# Add a row in the csv file for each line in the database
with open(path, "w", newline='') as csvfile:
for row in rows:
filewriter = csv.writer(csvfile)
filewriter.writerow(row)
self.err['text'] = "Data created successfully. We're set to go!"
# CSV file already existed
else:
self.err['text'] = "All data loaded successfully. Please enter values and choose your option."
# save results and close the database connection
conn.commit()
conn.close()

Answer 1

我能够在同事的帮助下解决这个问题。

问题：在程序逻辑导致文件丢失之前，我正在打开与"payrate.db"的连接。这样做会导致创建一个空表，以及随后的空.csv和.xml文件。

此外，我没有为我的 fetchall 语句分配查询，它失败了。