Shulou(Shulou.com)06/01 Report--

First, how to enter sqlite3 interactive mode for command operation?

1. Confirm whether sqlite3 has been installed

Go to the python command line and execute

> > import sqlite3 >

No error is reported, indicating that sqlite3 has been successfully installed

2. How to enter the sqlite3 command line

Sqlite3 / path/to/dbname

Just execute sqlite3 and add database name directly.

~ 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 () from 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 to the format listed below in this tutorial:

Sqlite > .header onsqlite > .mode columnsqlite > .timer onsqlite >

For more commands, see:

Http://www.runoob.com/sqlite/sqlite-commands.html

2. How does python execute the sqlite query command

The flow of python executing the sqlite command:

1. Cx = sqlite3.connect ("db.sqlite3)

Create or open a database file, or open it if the database file does not exist. Cx is a database connection object. It can do the following: commit ()-transaction commit rollback ()-transaction rollback close ()-close a database connection cursor ()-create a cursor

2. Cursor = cx.cursor ()

Defines a cursor. The cursor object has the following operations: execute ()-- execute sql statement executemany-- execute multiple sql statements close ()-- close cursor fetchone ()-- take a record fetchmany () from the result-- take multiple records fetchall () from the result-- take multiple records from the result scroll ()-- the method of cursor scrolling about objects can go to the Python home page to view detailed documents of DB API.

3. Cursor.execute ("". Select... (select count (1) from ucloud_uhost) as uhost... "")

Cursor.execute (sql statement) is the execution of sql statement

4. Cursor.close ()

Close the cursor

The following is the process of operating the database

> import sqlite3 > from django.db import connectionscx = sqlite3.connect ("/ Users/cengchengpeng/Downloads/django_test/cmdb/db.sqlite3") cursor = cx.cursor () > cursor > 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... "") > > cursor.description (('uhost', None, None), (' project', None, None), ('zone', 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 ()

Write python scripts to execute sqlite statements

# coding:utf-8from django.db import connectionsdef 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

The zip () method and the dict () method are used in the script.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.