First, how to enter the sqlite3 interactive mode for command operation?
1. Confirm whether the Sqlite3 is installed
Go to the python command line and execute
>>> Import sqlite3>>>
No error, indicating that Sqlite3 has successfully installed the
2. How to enter the Sqlite3 command line
Sqlite3/path/to/dbname
Directly execute sqlite3 Plus database name
~ sqlite3 ~/downloads/django_test/cmdb/db.sqlite3 sqlite3sqlite version 3.14.0 2016-07-26 15:17:14enter '. Help ' for Usage hints.sqlite>
3. Tables: View all Tables
sqlite> .tablesauth_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, the total number of data items in the query table
Select count () from TableName;
For example:
Sqlite> Select count () from Ucloud_zone;11sqlite> Select count () from Ucloud_uhost;147sqlite> Select count () fro M ucloud_project;10
5. Execute multiple query statements
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. Formatted output
You can use the following point commands to format the output as listed below for this tutorial:
Sqlite>.header Onsqlite>.mode Columnsqlite>.timer onsqlite>
More commands to view:
Http://www.runoob.com/sqlite/sqlite-commands.html
Ii. How Python executes sqlite query commands
Python performs the process of SQLite commands:
1, cx = Sqlite3.connect ("Db.sqlite3")
Creates or opens a database file, if the database file does not exist, is created, exists, and then opens the file. CX is a database connection object, It can have the following actions: Commit ()--Transaction commit rollback ()--transaction rollback close ()--Close a database connection cursor ()--Create a cursor
2. Cursor = Cx.cursor ()
A cursor was defined. The cursor object has the following actions: Execute ()--Execute SQL statement executemany--execute multiple SQL statements close ()--close cursor fetchone ()--Take a record from the result Fetchmany ()--take multiple records from the result Fetchall ()--remove multiple records from the results scroll ()--cursor scrolling about objects you can go to the Python home page to view detailed documentation of the DB API
3, Cursor.execute ("" "... select ... (select COUNT (1) from ucloud_uhost) as Uhost ... """)
Cursor.execute (SQL statement) is an Execute SQL statement
4, Cursor.close ()
Close Cursors
Here is the process of manipulating the database
>>> import sqlite3>>> from django.db import connectionscx = 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 () &Nbsp;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 ()
Write a Python script to execute SQLite statements
#coding: Utf-8from django.db import connectionsdef open_sql_dict (SQL, connection_name= ' default '): DBS = Connections[con Nection_name] cursor = Dbs.cursor () cursor.execute (sql) columns = [_[0].lower () for _ in Cursor.description] R Esults = [Dict (Zip (columns, _)) for _ in cursor] Cursor.close () return results
In this script, the zip () method and the Dict () method are used.
This article is from the "Zengestudy" blog, make sure to keep this source http://zengestudy.blog.51cto.com/1702365/1904680
Sqlite3 common Commands and how Django operates sqlite3 databases