系统组同事维护的trace 平台，之前是在PG数据库上，

之后因为某种原因，不用pg数据库了，改用sqlite 做为数据库了，
于是有了一个项目需要两套数据库的支持的现状，

最近决定合并下，一致同意弃用pg全部转到sqlite 平台。

sqlite 是一个单文件的数据库：

数据字典维护再文件头里面：

select * from sqlite_mater ;

显示所有的数据字典

select * from sqlite_master where type='table' 显示所有的见表语句，

这就好办了。

python 写段代码。一头连sqlite 一头连接pg

把pg里数据追加到sqlite里面，搞定。

下面是简单的python 的演示。

>>> import sqlite3 as sqlite
>>> dir sqlite
File "", line 1
    dir sqlite
             ^
SyntaxError: invalid syntax
>>> dir(sqlite) ;
['Binary', 'Cache', 'Connection', 'Cursor', 'DataError', 'DatabaseError', 'Date', 'DateFromTicks', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError', 'NotSupportedError', 'OperationalError', 'OptimizedUnicode', 'PARSE_COLNAMES', 'PARSE_DECLTYPES', 'PrepareProtocol', 'ProgrammingError', 'Row', 'SQLITE_ALTER_TABLE', 'SQLITE_ANALYZE', 'SQLITE_ATTACH', 'SQLITE_CREATE_INDEX', 'SQLITE_CREATE_TABLE', 'SQLITE_CREATE_TEMP_INDEX', 'SQLITE_CREATE_TEMP_TABLE', 'SQLITE_CREATE_TEMP_TRIGGER', 'SQLITE_CREATE_TEMP_VIEW', 'SQLITE_CREATE_TRIGGER', 'SQLITE_CREATE_VIEW', 'SQLITE_DELETE', 'SQLITE_DENY', 'SQLITE_DETACH', 'SQLITE_DROP_INDEX', 'SQLITE_DROP_TABLE', 'SQLITE_DROP_TEMP_INDEX', 'SQLITE_DROP_TEMP_TABLE', 'SQLITE_DROP_TEMP_TRIGGER', 'SQLITE_DROP_TEMP_VIEW', 'SQLITE_DROP_TRIGGER', 'SQLITE_DROP_VIEW', 'SQLITE_IGNORE', 'SQLITE_INSERT', 'SQLITE_OK', 'SQLITE_PRAGMA', 'SQLITE_READ', 'SQLITE_REINDEX', 'SQLITE_SELECT', 'SQLITE_TRANSACTION', 'SQLITE_UPDATE', 'Statement', 'Time', 'TimeFromTicks', 'Timestamp', 'TimestampFromTicks', 'Warning', '__builtins__', '__doc__', '__file__', '__name__', '__package__', '__path__', 'adapt', 'adapters', 'apilevel', 'complete_statement', 'connect', 'converters', 'datetime', 'dbapi2', 'enable_callback_tracebacks', 'enable_shared_cache', 'paramstyle', 'register_adapter', 'register_converter', 'sqlite_version', 'sqlite_version_info', 'threadsafety', 'time', 'version', 'version_info', 'x']

>>> conn=sqlite.connect('pg-sqlite.db')
>>> cur=conn.cursor()
>>> cur.execute('select * from sqlite_master');

>>> cur.fetchall()
[(u'table', u'system', u'system', 2, u'CREATE TABLE system (\n    name text PRIMARY KEY,\n    value text\n)'), (u'index', u'sqlite_autoindex_system_1', u'system', 3, None), (u'table', u'permission', u'permission', 4, u'CREATE TABLE permission (\n    username text,\n    action text,\n    UNIQUE (username,action)\n)'), (u'index', u'sqlite_autoindex_permission_1', u'permission', 5, None), (u'table', u'auth_cookie', u'auth_cookie', 6, u'CREATE TABLE auth_cookie (\n    cookie text,\n    name text,\n    ipnr text,\n    time integer,\n    UNIQUE (cookie,ipnr,name)\n)'), (u'index', u'sqlite_autoindex_auth_cookie_1', u'auth_cookie', 7, None), (u'table', u'session', u'session', 8, u'CREATE TABLE session (\n    sid text,\n    authenticated integer,\n    last_visit integer,\n    UNIQUE (sid,authenticated)\n)'), (u'index', u'sqlite_autoindex_session_1', u'session', 9, None), (u'index', u'session_last_visit_idx', u'session', 10, u'CREATE INDEX session_last_visit_idx ON session (last_visit)'), (u'index', u'session_authenticated_idx', u'session', 11, u'CREATE INDEX session_authenticated_idx ON session (authenticated)'), (u'table', u'session_attribute', u'session_attribute', 13, u'CREATE TABLE session_attribute (\n    sid text,\n    authenticated integer,\n    name text,\n    value text,\n    UNIQUE (sid,authenticated,name)\n)'), (u'index', u'sqlite_autoindex_session_attribute_1', u'session_attribute', 14, None), (u'table', u'cache', u'cache', 16, u'CREATE TABLE cache (\n    id text PRIMARY KEY,\n    generation integer\n)'), (u'index', u'sqlite_autoindex_cache_1', u'cache', 17, None), (u'table', u'attachment', u'attachment', 18, u'CREATE TABLE attachment (\n    type text,\n    id text,\n    filename text,\n    size integer,\n    time integer,\n    description text,\n    author text,\n    ipnr text,\n    UNIQUE (type,id,filename)\n)'), (u'index', u'sqlite_autoindex_attachment_1', u'attachment', 19, None), (u'table', u'wiki', u'wiki', 20, u'CREATE TABLE wiki (\n    name text,\n    version integer,\n    time integer,\n    author text,\n    ipnr text,\n    text text,\n    comment text,\n    readonly integer,\n    UNIQUE (name,version)\n)'), (u'index', u'sqlite_autoindex_wiki_1', u'wiki', 21, None), (u'index', u'wiki_time_idx', u'wiki', 22, u'CREATE INDEX wiki_time_idx ON wiki (time)'), (u'table', u'repository', u'repository', 24, u'CREATE TABLE repository (\n    id integer,\n    name text,\n    value text,\n    UNIQUE (id,name)\n)'), (u'index', u'sqlite_autoindex_repository_1', u'repository', 25, None), (u'table', u'revision', u'revision', 26, u'CREATE TABLE revision (\n    repos integer,\n    rev text,\n    time integer,\n    author text,\n    message text,\n    UNIQUE (repos,rev)\n)'), (u'index', u'sqlite_autoindex_revision_1', u'revision', 27, None), (u'index', u'revision_repos_time_idx', u'revision', 28, u'CREATE INDEX revision_repos_time_idx ON revision (repos,time)'), (u'table', u'node_change', u'node_change', 29, u'CREATE TABLE node_change (\n    repos integer,\n    rev text,\n    path text,\n    node_type text,\n    change_type text,\n    base_path text,\n    base_rev text,\n    UNIQUE (repos,rev,path,change_type)\n)'), (u'index', u'sqlite_autoindex_node_change_1', u'node_change', 30, None), (u'index', u'node_change_repos_rev_idx', u'node_change', 31, u'CREATE INDEX node_change_repos_rev_idx ON node_change (repos,rev)'), (u'table', u'ticket', u'ticket', 32, u'CREATE TABLE ticket (\n    id integer PRIMARY KEY,\n    type text,\n    time integer,\n    changetime integer,\n    component text,\n    severity text,\n    priority text,\n    owner text,\n    reporter text,\n    cc text,\n    version text,\n    milestone text,\n    status text,\n    resolution text,\n    summary text,\n    description text,\n    keywords text\n)'), (u'index', u'ticket_time_idx', u'ticket', 34, u'CREATE INDEX ticket_time_idx ON ticket (time)'), (u'index', u'ticket_status_idx', u'ticket', 35, u'CREATE INDEX ticket_status_idx ON ticket (status)'), (u'table', u'ticket_change', u'ticket_change', 36, u'CREATE TABLE ticket_change (\n    ticket integer,\n    time integer,\n    author text,\n    field text,\n    oldvalue text,\n    newvalue text,\n    UNIQUE (ticket,time,field)\n)'), (u'index', u'sqlite_autoindex_ticket_change_1', u'ticket_change', 37, None), (u'index', u'ticket_change_ticket_idx', u'ticket_change', 38, u'CREATE INDEX ticket_change_ticket_idx ON ticket_change (ticket)'), (u'index', u'ticket_change_time_idx', u'ticket_change', 39, u'CREATE INDEX ticket_change_time_idx ON ticket_change (time)'), (u'table', u'ticket_custom', u'ticket_custom', 41, u'CREATE TABLE ticket_custom (\n    ticket integer,\n    name text,\n    value text,\n    UNIQUE (ticket,name)\n)'), (u'index', u'sqlite_autoindex_ticket_custom_1', u'ticket_custom', 42, None), (u'table', u'enum', u'enum', 43, u'CREATE TABLE enum (\n    type text,\n    name text,\n    value text,\n    UNIQUE (type,name)\n)'), (u'index', u'sqlite_autoindex_enum_1', u'enum', 44, None), (u'table', u'component', u'component', 45, u'CREATE TABLE component (\n    name text PRIMARY KEY,\n    owner text,\n    description text\n)'), (u'index', u'sqlite_autoindex_component_1', u'component', 46, None), (u'table', u'milestone', u'milestone', 47, u'CREATE TABLE milestone (\n    name text PRIMARY KEY,\n    due integer,\n    completed integer,\n    description text\n)'), (u'index', u'sqlite_autoindex_milestone_1', u'milestone', 48, None), (u'table', u'version', u'version', 49, u'CREATE TABLE version (\n    name text PRIMARY KEY,\n    time integer,\n    description text\n)'), (u'index', u'sqlite_autoindex_version_1', u'version', 50, None), (u'table', u'report', u'report', 52, u'CREATE TABLE report (\n    id integer PRIMARY KEY,\n    author text,\n    title text,\n    query text,\n    description text\n)')]
>>> cur.execute("""select * from sqlite_master where type='table'""")

>>> cur.fetchone()
(u'table', u'system', u'system', 2, u'CREATE TABLE system (\n    name text PRIMARY KEY,\n    value text\n)')
>>> cur.fetchone()
(u'table', u'permission', u'permission', 4, u'CREATE TABLE permission (\n    username text,\n    action text,\n    UNIQUE (username,action)\n)')
>>> cur.fetchone()
(u'table', u'auth_cookie', u'auth_cookie', 6, u'CREATE TABLE auth_cookie (\n    cookie text,\n    name text,\n    ipnr text,\n    time integer,\n    UNIQUE (cookie,ipnr,name)\n)')
>>> cur.fetchone()
(u'table', u'session', u'session', 8, u'CREATE TABLE session (\n    sid text,\n    authenticated integer,\n    last_visit integer,\n    UNIQUE (sid,authenticated)\n)')
>>> cur.fetchone()
(u'table', u'session_attribute', u'session_attribute', 13, u'CREATE TABLE session_attribute (\n    sid text,\n    authenticated integer,\n    name text,\n    value text,\n    UNIQUE (sid,authenticated,name)\n)')
>>> cur.fetchone()
(u'table', u'cache', u'cache', 16, u'CREATE TABLE cache (\n    id text PRIMARY KEY,\n    generation integer\n)')
>>> cur.fetchone()
(u'table', u'attachment', u'attachment', 18, u'CREATE TABLE attachment (\n    type text,\n    id text,\n    filename text,\n    size integer,\n    time integer,\n    description text,\n    author text,\n    ipnr text,\n    UNIQUE (type,id,filename)\n)')
>>> cur.fetchone()
(u'table', u'wiki', u'wiki', 20, u'CREATE TABLE wiki (\n    name text,\n    version integer,\n    time integer,\n    author text,\n    ipnr text,\n    text text,\n    comment text,\n    readonly integer,\n    UNIQUE (name,version)\n)')
>>> cur.fetchone()
(u'table', u'repository', u'repository', 24, u'CREATE TABLE repository (\n    id integer,\n    name text,\n    value text,\n    UNIQUE (id,name)\n)')
>>> cur.fetchone()
(u'table', u'revision', u'revision', 26, u'CREATE TABLE revision (\n    repos integer,\n    rev text,\n    time integer,\n    author text,\n    message text,\n    UNIQUE (repos,rev)\n)')
>>> cur.fetchone()
(u'table', u'node_change', u'node_change', 29, u'CREATE TABLE node_change (\n    repos integer,\n    rev text,\n    path text,\n    node_type text,\n    change_type text,\n    base_path text,\n    base_rev text,\n    UNIQUE (repos,rev,path,change_type)\n)')
>>> cur.fetchone()
(u'table', u'ticket', u'ticket', 32, u'CREATE TABLE ticket (\n    id integer PRIMARY KEY,\n    type text,\n    time integer,\n    changetime integer,\n    component text,\n    severity text,\n    priority text,\n    owner text,\n    reporter text,\n    cc text,\n    version text,\n    milestone text,\n    status text,\n    resolution text,\n    summary text,\n    description text,\n    keywords text\n)')
>>> cur.fetchone()
(u'table', u'ticket_change', u'ticket_change', 36, u'CREATE TABLE ticket_change (\n    ticket integer,\n    time integer,\n    author text,\n    field text,\n    oldvalue text,\n    newvalue text,\n    UNIQUE (ticket,time,field)\n)')
>>> cur.fetchone()
(u'table', u'ticket_custom', u'ticket_custom', 41, u'CREATE TABLE ticket_custom (\n    ticket integer,\n    name text,\n    value text,\n    UNIQUE (ticket,name)\n)')
>>> cur.fetchone()
(u'table', u'enum', u'enum', 43, u'CREATE TABLE enum (\n    type text,\n    name text,\n    value text,\n    UNIQUE (type,name)\n)')
>>> cur.fetchone()
(u'table', u'component', u'component', 45, u'CREATE TABLE component (\n    name text PRIMARY KEY,\n    owner text,\n    description text\n)')
>>> cur.fetchone()
(u'table', u'milestone', u'milestone', 47, u'CREATE TABLE milestone (\n    name text PRIMARY KEY,\n    due integer,\n    completed integer,\n    description text\n)')
>>> cur.fetchone()
(u'table', u'version', u'version', 49, u'CREATE TABLE version (\n    name text PRIMARY KEY,\n    time integer,\n    description text\n)')
>>> cur.fetchone()
(u'table', u'report', u'report', 52, u'CREATE TABLE report (\n    id integer PRIMARY KEY,\n    author text,\n    title text,\n    query text,\n    description text\n)')
>>> cur.fetchone()
>>> cur.fetchone()
>>> cur.fetchone()

ITPub博客

sqlite 数据库的数据字典

最新文章