Sqlite3 common Commands and how Django operates the Sqlite3 database

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