使用Python将Excel转换为PostgreSQL [英] Excel to PostgreSQL using Python
本文介绍了使用Python将Excel转换为PostgreSQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我是Python的新手,我正尝试将excel直接导出到没有CSV文件的postgreSQL中.
I'm new to Python and I'm trying to export excel directly into postgreSQL without CSV files.
我不知道是否可能.
我一直遇到错误'列"daily_date"的类型为date,但表达式的类型为数字'
.
import psycopg2
import xlrd
book = xlrd.open_workbook("pytest.xlsx")
sheet = book.sheet_by_name("Source")
database = psycopg2.connect (database = "RunP", user="postgres", password="", host="localhost", port="5432")
cursor = database.cursor()
query = """INSERT INTO orders (Daily_Date, Days, First, Second, Leader) VALUES (%s, %s, %s, %s, %s)"""
for r in range(1, sheet.nrows):
Daily_Date = sheet.cell(r,0).value
Days = sheet.cell(r,1).value
First = sheet.cell(r,2).value
Second = sheet.cell(r,3).value
Leader = sheet.cell(r,4).value
values = (Daily_Date, Days, First, Second, Leader)
cursor.execute(query, values)
cursor.close()
database.commit()
database.close()
print ""
print ""
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "I just imported Excel into postgreSQL"
推荐答案
Excel将日期时间存储为数字.您可以使用 xlrd.xldate.xldate_as_datetime
进行转换:
Excel stores datetimes as numbers. You can use xlrd.xldate.xldate_as_datetime
to convert:
Daily_Date = xlrd.xldate.xldate_as_datetime(sheet.cell(r,0).value,book.datemode)
这篇关于使用Python将Excel转换为PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文