sqlite3常用命令以及django如
- 2020 年 1 月 9 日
- 筆記
一、如何進入sqlite3交互模式進行命令操作？
1、確認sqlite3是否已經安裝
進去python命令行，執行
>>> import sqlite3 >>>
沒有報錯，說明sqlite3已經成功安裝了
2、如何進入sqlite3命令行
sqlite3 /path/to/dbname
直接執行sqlite3 加資料庫名即可
~ 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、查詢表中總的數據條目數
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、執行多條查詢語句
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如何執行sqlite查詢命令
python執行sqlite命令的流程：
1、cx = sqlite3.connect("db.sqlite3)
創建或打開資料庫文件，如果資料庫文件不存在，則創建，存在，則打開該文件。cx為資料庫連接對象，它可以有以下操作： commit()–事務提交 rollback()–事務回滾 close()–關閉一個資料庫連接 cursor()–創建一個游標
2、cursor = cx.cursor()
定義了一個游標。游標對象有以下的操作： execute()–執行sql語句 executemany–執行多條sql語句 close()–關閉游標 fetchone()–從結果中取一條記錄 fetchmany()–從結果中取多條記錄 fetchall()–從結果中取出多條記錄 scroll()–游標滾動 關於對象的方法可以去 Python 主頁上查看DB API的詳細文檔
3、 cursor.execute(""" ... select ... (select count(1) from ucloud_uhost) as uhost ... """)
cursor.execute(sql語句)是執行sql語句
4、cursor.close()
關閉游標
下面是操作資料庫的過程
>>> 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腳本，來執行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
