【問(wèn)題描述】：python將EXCEL數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)時(shí)日期變成文本型數(shù)據(jù)并顯示為數(shù)字格式
【解決方案】
數(shù)據(jù)源：

codes:
#!/usr/bin/python3

-- coding: utf-8 --

數(shù)據(jù)表導(dǎo)入數(shù)據(jù)庫(kù)

import datetime import pyodbc import xlrd from datetime import datetime from xlrd import xldate_as_tuplei = 0def insert_data():global itry:db = pyodbc.connect(r'DRIVER={SQL Server Native Client 10.0};'r'SERVER=(local);'r'DATABASE=DBtest; UID=sa;PWD=726803')except pyodbc.InterfaceError as err:print(err)book = xlrd.open_workbook(r'C:\\Users\\Elink 001\\Desktop\\生產(chǎn)數(shù)據(jù)\\8號(hào)機(jī)\\table1.xls')sh = book.sheet_by_name('Sheet1') # 或者sheet = workbook.sheet(n)cursor = db.cursor()rows = sh.nrowscols = sh.ncolsprint(rows, cols)for i in range(1, rows): # 第一行是標(biāo)題名，對(duì)應(yīng)表中的字段名所以應(yīng)該從第二行開(kāi)始，計(jì)算機(jī)以0開(kāi)始計(jì)數(shù)，所以值是1cell = sh.cell_value(i, 1)date = datetime(*xldate_as_tuple(cell, 0))time = date.strftime('%Y/%m/%d %H:%M:%S')print(time)# print(time1)sql = "insert into TEST08 (時(shí)間) values ('%s')" % (time)cursor.execute(sql) # 執(zhí)行sql語(yǔ)句i = i+1db.commit() # 保存執(zhí)行結(jié)果至數(shù)據(jù)庫(kù)cursor.close() # 關(guān)閉連接db.close() # 關(guān)閉數(shù)據(jù)if __name__ == '__main__':insert_data()

測(cè)試結(jié)果：

【說(shuō)明】

cell = sh.cell_value(i, 1)date = datetime(*xldate_as_tuple(cell, 0))time = date.strftime('%Y/%m/%d %H:%M:%S')print(time)

• cell = sh.cell_value(i, 1)
  說(shuō)明：

def cell_value(self, rowx, colx):"Value of the cell in the given row and column."return self._cell_values[rowx][colx]

• date = datetime(*xldate_as_tuple(cell, 0))

#**功能：date→as_tuple，**def xldate_as_tuple(xldate, datemode):"""Convert an Excel number (presumed to represent a date, a datetime or a time) intoa tuple suitable for feeding to datetime or mx.DateTime constructors.:param xldate: The Excel number:param datemode: 0: 1900-based, 1: 1904-based.:raises xlrd.xldate.XLDateNegative::raises xlrd.xldate.XLDateAmbiguous::raises xlrd.xldate.XLDateTooLarge::raises xlrd.xldate.XLDateBadDatemode::raises xlrd.xldate.XLDateError::returns: Gregorian ``(year, month, day, hour, minute, nearest_second)``... warning::When using this function to interpret the contents of a workbook, youshould pass in the :attr:`~xlrd.book.Book.datemode`attribute of that workbook. Whether the workbook has ever been anywherenear a Macintosh is irrelevant... admonition:: Special caseIf ``0.0 <= xldate < 1.0``, it is assumed to represent a time;``(0, 0, 0, hour, minute, second)`` will be returned... note::``1904-01-01`` is not regarded as a valid date in the ``datemode==1``system; its "serial number" is zero."""if datemode not in (0, 1):raise XLDateBadDatemode(datemode)if xldate == 0.00:return (0, 0, 0, 0, 0, 0)if xldate < 0.00:raise XLDateNegative(xldate)xldays = int(xldate)frac = xldate - xldaysseconds = int(round(frac * 86400.0))assert 0 <= seconds <= 86400if seconds == 86400:hour = minute = second = 0xldays += 1else:# second = seconds % 60; minutes = seconds // 60minutes, second = divmod(seconds, 60)# minute = minutes % 60; hour = minutes // 60hour, minute = divmod(minutes, 60)if xldays >= _XLDAYS_TOO_LARGE[datemode]:raise XLDateTooLarge(xldate)if xldays == 0:return (0, 0, 0, hour, minute, second)if xldays < 61 and datemode == 0:raise XLDateAmbiguous(xldate)jdn = xldays + _JDN_delta[datemode]yreg = ((((jdn * 4 + 274277) // 146097) * 3 // 4) + jdn + 1363) * 4 + 3mp = ((yreg % 1461) // 4) * 535 + 333d = ((mp % 16384) // 535) + 1# mp /= 16384mp >>= 14if mp >= 10:return ((yreg // 1461) - 4715, mp - 9, d, hour, minute, second)else:return ((yreg // 1461) - 4716, mp + 3, d, hour, minute, second)

• time = date.strftime(’%Y/%m/%d %H:%M:%S’)

# 時(shí)間格式轉(zhuǎn)換，保留def strftime(self, fmt: _Text) -> str: ...if sys.version_info >= (3,):def __format__(self, fmt: str) -> str: ...else:def __format__(self, fmt: AnyStr) -> AnyStr: ...

總結(jié)

以上是生活随笔為你收集整理的python将EXCEL数据导入数据库时日期型数据变成数字并加.0的问题一行代码解决方案方案的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。