Operations and Learning Python Reptile Intermediate (vii) SQLITE3

The previous article has been described without database version operation (Csv,json), today we will start to talk about the database version of the operation, the first is sqlite3.

1 Introduction

SQLite is a C library that provides a lightweight disk-based database that does not require a separate server process and allows access to the database using non-standard formats for the SQL query language. Some applications can use SQLite for internal data storage. You can also use SQLite to prototype your application and then port the code to a larger database, such as PostgreSQL or Oracle.

2 Sqlite3 Simple to use

The Sqlite3 module is written by Gerhard H?ring, which provides a SQL interface that is compatible with the DB-API 2.0 specification described in Pep 249. To use this module, you must first create a Connection object that represents the database. The data here will be stored in the example. DB file:

# -*- coding: utf-8 -*-import sqlite3# 创建数据库连接对象，存储在test.db中conn = sqlite3.connect(‘test.db‘)

You can also provide a special name: Memory: Creates a database in RAM.
Once you have a connection object, you can create a cursor object and invoke its execute () method to execute the SQL command:

# -*- coding: utf-8 -*-import sqlite3conn = sqlite3.connect(‘test.db‘)# 创建游标对象c = conn.cursor()# 创建表c.execute("CREATE TABLE stocks(data TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL)")# 插入数据c.execute("INSERT INTO stocks VALUES (‘2018-01-14‘, ‘buy‘, ‘rhat‘, 100, 35.14)")# 保存数据conn.commit()# 关闭连接conn.close()

After execution, a test.db file is generated locally, the persisted data is persistent, and is available in subsequent sessions (I am using the WIN10 + pycharm environment), and a database tool can be downloaded Navicat Premium to view the contents of the Test.db file, install the software online very good search, do not explain, I will connect the database operation instructions:

Typically, your SQL operation requires the use of values from Python variables. You should not use Python's string manipulation to assemble your query because it is unsafe to make your program vulnerable to SQL injection attacks. Instead, use the DB-API parameter substitution. Take it? As a placeholder for the value you want to use, and then provide a tuple as the second parameter, as the second parameter of the Execute () method of the cursor. (Other database modules may use different placeholders, such as%s) for example:

# 不要这么做# 定义变量symbol = ‘rhat‘# 获取查询结果dbs = c.execute("SELECT * FROM stocks WHERE symbol=‘%s‘" % symbol)# 打印出数据for db in dbs:    print(db)# 正确做法t = (‘rhat‘, )c.execute(‘SELECT * FROM stocks WHERE symbol=?‘, t)print(c.fetchone())# 插入多条数据purchases = [(‘2006-03-28‘, ‘BUY‘, ‘IBM‘, 1000, 45.00),             (‘2006-03-29‘, ‘BUY‘, ‘ThinkPad‘, 5000, 55.00),             (‘2006-03-30‘, ‘SELL‘, ‘IBM‘, 100, 33.00),             ]c.executemany(‘INSERT INTO stocks VALUES (?, ?, ?, ?, ?)‘, purchases)conn.commit()conn.close()

After executing a SELECT statement, you can retrieve the data as an iterator, or you can call the cursor's Fetchone () method to retrieve a single matching row, or call Fetchall () to get a list of all matching rows.

# 迭代器方式获取内容for row in c.execute(‘SELECT * FROM stocks ORDER BY price‘):    print(row)# fetchone和fetchall方式获取内容c.execute(‘SELECT * FROM stocks ORDER BY price‘)print(c.fetchone())print(c.fetchall())

3 Sqlite3 Common Modules

name	explain
Sqlite3.connect (database[, timeout, other arguments])	Open a connection to the SQLite database file database. You can use ": Memory:" To open a database connected to a database stored in RAM instead of on disk. When a database is accessed by multiple connections, and one process modifies the database, the SQLite database is locked until the transaction is committed. The time-out parameter specifies how long the connection should wait until an exception is thrown. The default value for the timeout parameter is 5.0 (5 seconds).
Conn. Cursor ()	The routine creates a cursor that will be used in Python database programming. The method accepts a single optional parameter, Cursorclass. If this argument is provided, it must be an extended self-sqlite3. The cursor's custom cursor class.
Conn.commit ()	Commits the current transaction. If you do not call this method, anything you do after the last call to commit () is not visible from the other database connections.
Conn.rollback ()	This method rolls back the database to the last change after commit () was called.
Conn.close ()	This will close the database connection. Note that this does not automatically invoke commit (). If you close the database connection without calling commit (), your changes will be lost!
Conn.execute (sql[, parameters])	This is a nonstandard shortcut that creates a cursor object by calling the cursor () method and invokes the Execute () method of the cursor with the given parameters and returns the cursor.
Conn.executemany (sql[, parameters])	Same as Execute method, can pass multiple parameters simultaneously
Conn.executescript (Sql_script)	You can pass in the SQL script directly, Sqlscript can be an instance of Str.
Conn.total_changes ()	Returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.
Cursor.fetchone ()	Gets the next row of the query result set, returns a separate sequence, or returns none if no more data is available.
Cursor.fetchmany (Size=cursor.arraysize)	Gets the next set of rows for the query results, returning a list. When no more rows are available, an empty list is returned. The number of rows per call is specified by the size parameter. If not given, the arraysize of the cursor determines the number of rows to get.
Cursor.fetchall ()	Gets all (remaining) rows of the query results, returning a list. Note that the ArraySize property of the cursor can affect the performance of the operation. Returns an empty list when no rows are available.
Sqlite3. Row	The row instance acts as a highly optimized row_factory of the connection object. It attempts to emulate a tuple in most features. It supports mapping access for column names and indexes, iterations, representations, equality tests, and Len (). If two row objects have the same columns, and their members are equal, they are more equal.
Row.keys ()	The method returns a list of column names. After the query, it is the first member in each tuple.

4 Example

Connection and Cursor determination

import sqlite3# 创建conn实例conn = sqlite3.connect(‘test.db‘)# 创建游标对象c = conn.cursor()

Increase

# 创建表c.execute("CREATE TABLE stocks(data TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL)")# 插入单条数据c.execute("INSERT INTO stocks VALUES (‘2018-01-14‘, ‘buy‘, ‘rhat‘, 100, 35.14)")# 插入多条数据purchases = [(‘2006-03-28‘, ‘BUY‘, ‘IBM‘, 1000, 45.00),             (‘2006-03-29‘, ‘BUY‘, ‘ThinkPad‘, 5000, 55.00),             (‘2006-03-30‘, ‘SELL‘, ‘IBM‘, 100, 33.00),             ]c.executemany(‘INSERT INTO stocks VALUES (?, ?, ?, ?, ?)‘, purchases)

By deleting

# 删除c.execute("DELETE from stocks WHERE data=‘2018-01-14‘;")

Change

# 修改数据c.execute("UPDATE stocks set price = 25000.00 where symbol=‘ThinkPad‘")

Check

# 迭代器方式获取内容for row in c.execute(‘SELECT * FROM stocks ORDER BY price‘):    print(row)# fetchone和fetchall方式获取内容c.execute(‘SELECT * FROM stocks ORDER BY price‘)print(c.fetchone())print(c.fetchall())

The above is the basic additions and deletions to check the operation, remember must commit, and normal close mode closed, so as not to cause resource leakage.

Operations and Learning Python Reptile Intermediate (vii) SQLITE3