python - Python PostgreSQL - 导出到 CSV 不导出所有行
问题描述
我有一个 Postgresql SQL 查询,我想用 Python 运行并将其导出到 CSV 文件。
我对 Python 很陌生,但我设法编写了一个可以运行查询并导出到文件的脚本。
import psycopg2
# File path and name.
fileName = 'test.csv'
# Database connection variable.
connect = None
# Check if the file path exists.
if os.path.exists(filePath):
try:
# Connect to database.
connect = psycopg2.connect(host="xxxx", port="5439", database="xxxx", user="xxxx", password="xxxx")
except psycopg2.DatabaseError as e:
# Confirm unsuccessful connection and stop program execution.
print("Database connection unsuccessful.")
quit()
# Cursor to execute query.
cursor = connect.cursor()
# SQL to select data from the person table.
sqlSelect = """
SELECT * FROM TABLE
"""
try:
# Execute query.
cursor.execute(sqlSelect)
# Fetch the data returned.
results = cursor.fetchall()
# Extract the table headers.
headers = [i[0] for i in cursor.description]
#Print the results
#print(pd.read_sql(sqlSelect, connect))
print(tb.tabulate(results, headers=headers, tablefmt='psql', showindex="always", floatfmt=".10f"))
# Open CSV file for writing.
csvFile = csv.writer(open(filePath + fileName, 'w', newline=''),
delimiter=',', lineterminator='\r\n',
quoting=csv.QUOTE_ALL, escapechar='\\')
# Add the headers and data to the CSV file.
csvFile.writerow(headers)
for row in results:
csvFile.writerow(row)
# Message stating export successful.
print("Data export successful.")
# csvFile.close()
except psycopg2.DatabaseError as e:
# Message stating export unsuccessful.
print("Data export unsuccessful.")
quit()
finally:
# Close database connection.
cursor.close()
connect.close()
else:
# Message stating file path does not exist.
print("File path does not exist.")
cursor.close()
connect.close()
我运行的查询生成了 70 行结果(当我通过数据库程序运行它时)。但是,当我将数据导出到 CSV 时,它只导出 48 行。
我不知道出了什么问题。
解决方案
您的代码在 postgres 中有 490 行对我有用。
也许您的问题出在数据库中,其中某些记录具有某些特殊字符,导致代码停止并且无法获得预期结果。
调试您的脚本,添加异常打印以检查是否是问题所在。
推荐阅读
- javascript - 有没有办法让javascript在svg世界地图上绘制纬度和经度线?
- node.js - Puppeteer 性能泄漏
- swift - 有没有一种特定的方法可以将 DispatchWorkItems 附加到 DispatchQueue 而不是在代码中重新声明它们?
- php - 为什么
- python - Creating an object that asks the user to enter a desired user id and stores this id
- powershell - 如何修复无法转换 system.object[] 错误
- vba - 根据多列中的重复项组合、求和并取最大值
- angular - 有没有办法在构建时修改装饰器选项?
- c++ - __LINE__ 在 MSVC 中不是 constexpr
- batch-file - Windows Batch - 在扩展名之前重命名带有句点的文件