一笨觅、如何進(jìn)入sqlite3交互模式進(jìn)行命令操作？
1票顾、確認(rèn)sqlite3是否已經(jīng)安裝
進(jìn)去python命令行志膀，執(zhí)行
>>> import sqlite3
>>>
沒有報錯乎芳，說明sqlite3已經(jīng)成功安裝了
2哆姻、如何進(jìn)入sqlite3命令行
sqlite3 /path/to/dbname
直接執(zhí)行sqlite3 加數(shù)據(jù)庫名即可
~ sqlite3 ~/Downloads/django_test/cmdb/db.sqlite3
sqlite3SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite>
3预吆、.tables ：查看所有表
sqlite> .tables
auth_group django_content_type
auth_group_permissions django_migrations
auth_permission django_session
auth_user ucloud_project
auth_user_groups ucloud_region
auth_user_user_permissions ucloud_uhost
django_admin_log ucloud_zone
4薄嫡、查詢表中總的數(shù)據(jù)條目數(shù)
select count() from TableName;
例如：
sqlite> select count() from ucloud_zone;
11
sqlite> select count() from ucloud_uhost;
147
sqlite> select count() from ucloud_project;
10
5饥努、執(zhí)行多條查詢語句
sqlite> select
...> (select count(1) from ucloud_uhost) as uhost,
...> (select count(1) from ucloud_project) as project,
...> (select count(1) from ucloud_region) as region
...> ;
147|10|8
6捡鱼、格式化輸出
您可以使用下列的點命令來格式化輸出為本教程下面所列出的格式：
sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>
更多命令查看：
http://www.runoob.com/sqlite/sqlite-commands.html
二、python如何執(zhí)行sqlite查詢命令
python執(zhí)行sqlite命令的流程：
1酷愧、cx = sqlite3.connect("db.sqlite3)
創(chuàng)建或打開數(shù)據(jù)庫文件驾诈，如果數(shù)據(jù)庫文件不存在，則創(chuàng)建溶浴，存在乍迄，則打開該文件。cx為數(shù)據(jù)庫連接對象士败，它可以有以下操作： commit()--事務(wù)提交 rollback()--事務(wù)回滾 close()--關(guān)閉一個數(shù)據(jù)庫連接 cursor()--創(chuàng)建一個游標(biāo)
2闯两、cursor = cx.cursor()
定義了一個游標(biāo)。游標(biāo)對象有以下的操作： execute()--執(zhí)行sql語句 executemany--執(zhí)行多條sql語句 close()--關(guān)閉游標(biāo) fetchone()--從結(jié)果中取一條記錄 fetchmany()--從結(jié)果中取多條記錄 fetchall()--從結(jié)果中取出多條記錄 scroll()--游標(biāo)滾動 關(guān)于對象的方法可以去 Python 主頁上查看DB API的詳細(xì)文檔
3谅将、 cursor.execute("""
... select
... (select count(1) from ucloud_uhost) as uhost
... """)
cursor.execute(sql語句)是執(zhí)行sql語句
4漾狼、cursor.close()
關(guān)閉游標(biāo)
下面是操作數(shù)據(jù)庫的過程
>>> import sqlite3
>>> from django.db import connections
cx = sqlite3.connect("/Users/cengchengpeng/Downloads/django_test/cmdb/db.sqlite3")
cursor = cx.cursor()
>>> cursor
<sqlite3.Cursor object at 0x10b24cb20>
>>> cursor.execute("""
... select
... (select count(1) from ucloud_uhost) as uhost,
... (select count(1) from ucloud_project) as project,
... (select count(1) from ucloud_zone) as zone
... """)
<sqlite3.Cursor object at 0x10b24cb20>
>>> cursor.description
(('uhost', None, None, None, None, None, None), ('project', None, None, None, None, None, None), ('zone', None, None, None, None, None, None))
>>> columns = [_[0].lower() for _ in cursor.description]
>>> columns
['uhost', 'project', 'zone']
>>> for _ in cursor:
... print _
...
(147, 10, 11)
>>> results = [dict(zip(columns, _)) for _ in cursor]
>>> results
>>> results
[{'project': 10, 'zone': 11, 'uhost': 147}]
>>> cursor.close()
寫python腳本，來執(zhí)行sqlite語句
#coding:utf-8
from django.db import connections
def open_sql_dict(sql, connection_name='default'):
dbs = connections[connection_name]
cursor = dbs.cursor()
cursor.execute(sql)
columns = [_[0].lower() for _ in cursor.description]
results = [dict(zip(columns, _)) for _ in cursor]
cursor.close()
return results
