Как экспортировать дату datetime?

Question

Не могу экспортировать дату:

import xlwt
from tempfile import TemporaryFile
book = xlwt.Workbook()
sheet1 = book.add_sheet('sheet1')
cur.execute('SELECT date, name from table')
rows = cur.fetchall()
for row, array in enumerate(rows):
    for col, value in enumerate(array):
        sheet1.write(row, col, value)

name = "this.xls"
book.save(name)
book.save(TemporaryFile())

Вместо 17/08/2020 выдает 44044, хотя Name выдает верный.

Answer 1

Воспользуйтесь модулем Pandas.

Пример:

import pandas as pd
import cx_Oracle
from sqlalchemy import types, create_engine

#######################################################
### Oracle DB TNS description / connection string
#######################################################
tns = """
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my-db-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = my_service_name)
    )
  )
"""

usr = "test"
pwd = "my_oracle_password"

engine = create_engine(f'oracle+cx_oracle://{usr}:{pwd}@{tns}')

output_fn = r'/path/to/this.xlsx'
qry = 'SELECT date, name from table'
pd.read_sql(qry, engine).to_excel(output_fn, sheet_name='Sheet1', index=False)

Answer 2

как можно добавить переменную в запрос (qry)?

import pandas as pd
import cx_Oracle
from cx_Oracle import makedsn
from sqlalchemy import types, create_engine

usr = "test"
pwd = "my_oracle_password"
dsn = makedsn("ora_scan_or_hostname", 1521, service_name="my_service_name")

engine = create_engine(f'oracle+cx_oracle://{usr}:{pwd}@{dsn}')

output_fn = r'/path/to/this.xlsx'
qry = 'SELECT date, name from table where name = :name_var'
name = 'Joe'
pd.read_sql(qry, engine, params=(name, )).to_excel(output_fn, sheet_name='Sheet1', index=False)
# NOTE: ---------------> ^^^^^^^^^^^^^^^

БЛОГ НА HUSL

Как экспортировать дату datetime?

Ответы (2 шт):