python database 日期_Python SQLite3数据库日期与时间常见函数用法分析

本文实例讲述了Python SQLite3数据库日期与时间常见函数。分享给大家供大家参考，具体如下：

import sqlite3

#con = sqlite3.connect('example.db')

con = sqlite3.connect(":memory:")

c = con.cursor()

# Create table

c.execute('''CREATE TABLE stocks

(date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data

c.execute("INSERT INTO stocks VALUES (?,?,?,?,?)", ('2006-03-27','BUY','RHAT',100,60.14))

# Larger example that inserts many records at a time

purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),

('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),

('2006-04-06', 'SELL', 'IBM', 500, 53.00),

('2006-04-07', 'SELL', 'MSFT', 500, 74.00),

('2006-04-08', 'SELL', 'IBM', 500, 54.00),

('2006-04-09', 'SELL', 'MSFT', 500, 73.00),

('2006-04-10', 'SELL', 'MSFT', 500, 75.00),

('2006-04-12', 'SELL', 'IBM', 500, 55.00),

]c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

# Save (commit) the changes

con.commit()

# Do this instead

t = ('RHAT',)

c.execute('SELECT * FROM stocks WHERE symbol=?', t)

#print(c.fetchone())

#for row in c.execute('SELECT * FROM stocks ORDER BY price'):

# print(row)

#for row in c.execute('SELECT * FROM stocks LIMIT 5 OFFSET 0'):

# print(row)

for row in c.execute('SELECT * FROM stocks LIMIT 5 OFFSET 1'):

print(row)

#Select Top N * From

# ====================================================================================

# SQLite 日期 & 时间

# ====================================================================================

print('='*30)

print('SQLite 日期 & 时间')

print('='*30)

# 计算当前日期

c.execute("SELECT date('now')")

print(c.fetchone())

# 计算当前月份的最后一天：

c.execute("SELECT date('now','start of month','+1 month','-1 day');")

print(c.fetchone())

# 计算给定 UNIX 时间戳 1092941466 的日期和时间：

c.execute("SELECT datetime(1092941466, 'unixepoch');")

print(c.fetchone())

# 计算给定 UNIX 时间戳 1092941466 相对本地时区的日期和时间：

c.execute("SELECT datetime(1092941466, 'unixepoch', 'localtime');")

print(c.fetchone())

# 计算当前的 UNIX 时间戳：

c.execute("SELECT datetime(1092941466, 'unixepoch', 'localtime');")

print(c.fetchone())

# 计算美国"独立宣言"签署以来的天数：

c.execute("SELECT julianday('now') - julianday('1776-07-04');")

print(c.fetchone())

# 计算从 2004 年某一特定时刻以来的秒数：

c.execute("SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');")

print(c.fetchone())

# 计算当年 10 月的第一个星期二的日期：

c.execute("SELECT date('now','start of year','+9 months','weekday 2');")