本文整理自网络,侵删。
代码如下:
import sqlite3
conn = sqlite3.connect('/tmp/sqlite.db')
cur = conn.cursor()
接下来干嘛呢?建一张表吧。这里需要注意的是,SQLite不支持在创建表的同时创建索引,所以要分两步走,先创建表然后再创建索引
import sqlite3
conn = sqlite3.connect('/tmp/sqlite.db')
cur = conn.cursor()
接下来干嘛呢?建一张表吧。这里需要注意的是,SQLite不支持在创建表的同时创建索引,所以要分两步走,先创建表然后再创建索引
代码如下:
create_table_stmt = '''CREATE TABLE IF NOT EXISTS test_table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
duration INTEGER,
event_date TEXT,
parameter TEXT );'''
id INTEGER PRIMARY KEY AUTOINCREMENT,
duration INTEGER,
event_date TEXT,
parameter TEXT );'''
create_index = 'CREATE INDEX IF NOT EXISTS idx_id ON test_table (id);'
cur.execute(create_table_stmt)
cur.execute(create_index)
conn.commit()
然后往里面插一点数据吧,SQLite只支持5种基本的数据类型
代码如下:
NULL. The value is a NULL value
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)
BLOB. The value is a blob of data, stored exactly as it was input
NULL. The value is a NULL value
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)
BLOB. The value is a blob of data, stored exactly as it was input
问题来了,SQLite的时间和日期类型在哪里?原来SQLite可以把时间日期保存在一下几种数据类型里面
代码如下:
TEXT as ISO8601 strings ('YYYY-MM-DD HH:MM:SS.SSS').
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
TEXT as ISO8601 strings ('YYYY-MM-DD HH:MM:SS.SSS').
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
insert_stmt = 'insert into test_table values (?, ?, ?)'
record = (123, '2011-11-30 12:34:56', 'hello world')
cur.execute( insert_stmt, record )
conn.commit()
把日期保存为字符串以后,不能直接拿出来直接当日期用,在用之前要调用SQLite的date函数
例如找前一天存进去的数据:
代码如下:
SELECT
id,
duration,
event_date,
parameter
FROM test_table
WHERE
DATE(event_date) = DATE('now', '-1 day', 'localtime')
ORDER BY id, event_date
书籍
SELECT
id,
duration,
event_date,
parameter
FROM test_table
WHERE
DATE(event_date) = DATE('now', '-1 day', 'localtime')
ORDER BY id, event_date
查看表结构 select * from sqlite_master
查看表信息 PRAGMA table_info (table_name)
SQLite中的时间日期函数
相关阅读 >>
php轻量级数据库操作类medoo增加、删除、修改、查询例子
android 使用vitamio打造自己的万能播放器(4)——本地播放(快捷搜索、数据存储)
更多相关阅读请进入《Sqlite》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
相关推荐
评论
管理员已关闭评论功能...
- 欢迎访问木庄网络博客
- 可复制:代码框内的文字。
- 方法:Ctrl+C。