Details of how Python operates the SQLite database

This article gives an example of how Python operates on the SQLite database. To share for your reference, specific as follows:

SQLite brief introduction

SQLite database is a very small embedded open source database software, which means there is no independent maintenance process, all maintenance comes from the program itself. It is an es9EN-compliant relational database management system that is designed to be embedded and is currently used in many embedded products. Its footprint is so low that in embedded devices, only a few hundred K may be enough. It can support Windows/Linux/Unix and other mainstream operating systems. At the same time, it can combine with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as the ODBC interface. It is also faster than Mysql and PostgreSQL, two world-renowned open source database management systems. SQLite the first Alpha version was released in May 2000. It has been 10 years since the release of SQLite 3.

Installation and use

1. Import Python SQLITE database module

After Python2.5, SQLite3 is built in and becomes the built-in module, which saves us the installation effort and only needs to import ~

import sqlite3

2. Create/open database

When you call the connect function, specify the library name, open the specified database if it exists, or create a new one if it doesn't.

cx = sqlite3.connect("E:/test.db")

You can also create a database in memory.

con = sqlite3.connect(":memory:")

3. Database connection objects

The object cx returned when the database is opened is a database connection object, which can have the following operations:

commit()-- Transaction commit
rollback()-- Transaction rollback
close()-- close 1 database connection
cursor()-- create a cursor

On commit(), if isolation_level isolation level defaults, you will need to use this command for every database operation, or you can set isolation_level=None, which makes it into auto commit mode.

4. Use a cursor to query the database

We need to query the database using the cursor object SQL statement to get the query object. Define a cursor by the following method.

cu=cx.cursor()

A cursor object has the following operations:

execute()-- Execute sql statement
executemany-- execute multiple sql statements
close()-- Close the cursor
fetchone()-- take one record from the result and point the cursor to the next record
fetchmany()-- Take multiple records from the results
fetchall()-- Extract all records from the results
scroll()-- cursor scrolling

1. The table

cu.execute("create table catalog (id integer primary key,pid integer,name varchar(10) UNIQUE,nickname text NULL)")

The above statement creates a table called catalog, which has 1 primary key id, 1 pid, and 1 name, which cannot be repeated, and 1 nickname which defaults to NULL.

2. Insert data

Please avoid the following:

# Never do this -- insecure  This results in an injection attack 
pid=200
c.execute("... where pid = '%s'" % pid)

The correct approach is to use the form t=(n,) if t is a single value, because tuples are immutable.

for t in[(0,10,'abc','Yu'),(1,20,'cba','Xu')]:
  cx.execute("insert into catalog values (?,?,?,?)", t)

Simply insert two rows of data, but be warned that this will not take effect until it is committed. We use the database connection object commit to commit and roll back rollback.

cx.commit()

3. The query

cu.execute("select * from catalog") 

To extract the queried data, use the cursor's fetch function, such as:

In [10]: cu.fetchall()
Out[10]: [(0, 10, u'abc', u'Yu'), (1, 20, u'cba', u'Xu')]

If we use cu.fetchone (), we first return item 1 in the list, then we use item 2 again, and so on.

4. Modify

In [12]: cu.execute("update catalog set name='Boy' where id = 0")
In [13]: cx.commit()

Note that the modified data is submitted later

5. Remove

cu.execute("delete from catalog where id = 1") 
cx.commit() 

6. Use Chinese

Please make sure that your IDE or system default code is utf-8 and add u before Chinese

cx = sqlite3.connect("E:/test.db")

0

If you want to display a Chinese font, you need to print out each string in turn

cx = sqlite3.connect("E:/test.db")

1

7. Row type

Row provides index-based and name-sensitive access to columns with little memory overhead. The text reads as follows:

sqlite3.Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db_row based solution.

Details of the Row object

class sqlite3.Row
A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a tuple in most of its features.
It supports mapping access by column name and index, iteration, representation, equality testing and len().
If two Row objects have exactly the same columns and their members are equal, they compare equal.
Changed in version 2.6: Added iteration and equality (hashability).
keys()
This method returns a tuple of column names. Immediately after a query, it is the first member of each tuple in Cursor.description.
New in version 2.6.

Here's an example

cx = sqlite3.connect("E:/test.db")

2

Keyword queries using columns

In [43]: r['id']
Out[43]: 0
In [44]: r['name']
Out[44]: u'\u9c7c'

For more information about Python, please visit our special topics: Python common database operations skills summary, Python coding skills summary, Python pictures skills summary, "Python data structure and algorithm tutorial", "Python Socket programming skills summary", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article has been helpful in Python programming.