This article is participating in Python Theme Month. See the link for details.

1. Insert a single row of data

import sqlite3
con = sqlite3.connect('xxxx/test.db')
cur = con.cursor()  Create a cursor object
cur.execute("create table Student(SNO char(10) UNIQUE primary key,Sname char(20),Ssex char(2),Sage SMALLINT,Sdept char(20));")# The last line in quotation marks is an SQL statement, and the semicolon at the end of the statement is optional
Copy the code

Method one:

data = "170141000,' Zhang SAN ',' male ',20,'EC'"
cur.execute('INSERT INTO Student VALUES (%s)' % data)
Copy the code

Method 2:

cur.execute('INSERT INTO Student VALUES(? ,? ,? ,? ,?) ', (170141000.'bright'.'male'.21.'Blooming hemp'))# the execute perform
con.commit()# commit to submit
Copy the code

Method 3:

cur.execute("INSERT INTO sc (Sno,Sname,Sage,Sdept) values(cno, Sno, Sage,Sdept) VALUES (cNO, Sno, Sage,Sdept))
con.commit()
Copy the code

2. Insert multiple rows of data

data_ToBeInserted = ["170140009,' fan ',' male ',20,' Humanities '"."170140000,' xiang ',' male ',20,'GIS'"."170140001,' Hao ',' Male ',20,'GIS'"."170140002,' Fei ',' Male ',20,' Humanities '"."170100001,' Liang ',' Nan ',27,' Begonia 2nd Floor '"]
sql_insert = "INSERT INTO Student VALUES"   # SQL statements
sql_values = ""     SQL statements 2 #
for i in range(0.len(data_ToBeInserted)):   Table subscript index, one row at a time to extract data
    sql_values += '('                       Add the left parenthesis required by the execute statement
    sql_values += data_ToBeInserted[i]      # insert data
    sql_values += '), '                      # right parenthesis
sql_values = sql_values.strip(', ')          # Remove the comma from the last line of data, or replace the semicolon
sql_todo = sql_insert + sql_values
Copy the code

3. Check whether a table exists in SQLite. If not, create a table

create_tb_cmd=''' CREATE TABLE IF NOT EXISTS USER (NAME TEXT, AGE INT, SALARY REAL); ' ' '
conn.execute(create_tb_cmd)
Copy the code

4. How do I list all tables and indexes in an SQLite database

In a C/C++ program (or scripting language using Tcl/Ruby/Perl/Python, etc.) you can perform a SELECT query on a special name called SQLITE_MASTER to get the index of all the tables. Every SQLite database has a table called SQLITE_MASTER, which defines the schema for the database. The SQLITE_MASTER table looks like this:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);
Copy the code

You can query all fields in a table by using the following statement

PRAGMA table_info([tablename])
Copy the code

For a table, the type field is always ‘table’ and the name field is always the name of the table. So, to get a list of all the tables in the database, use the following SELECT statement:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;
Copy the code

Select * from table where type = ‘table’ and name = ‘table’; Type = ‘index’, name = index, tbl_name = name of table owned by index

SQL fields, whether TABLE or INDEX, are the command text from when they were originally created using the CREATE TABLE or CREATE INDEX statement. The SQL field is NULL for indexes that are automatically created to implement the PRIMARY KEY or UNIQUE constraint.

The SQLITE_MASTER table is read-only. You cannot use UPDATE, INSERT, or DELETE on it. It is automatically updated by the CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands.

Temporary tables do not appear in the SQLITE_MASTER table. Temporary tables, with their indexes and triggers, are stored in another table called SQLITE_TEMP_MASTER. SQLITE_TEMP_MASTER is similar to SQLITE_MASTER, but it is only visible to applications that create those temporary tables. To get a list of all tables, whether permanent or temporary, you can use a command like the following:

SELECT name FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name
Copy the code

The program can get all the table information through the sqlite_master table.

select type, name, tbl_name from sqlite_master order by type
Copy the code

Sqlite_master has the following structure

insert or replace into table_name( id.type) values (1.0);
Copy the code

Method 2:

insert or ignore into table_name (id.type) values (2.0);
Copy the code

Method 3:

IF NOT EXISTS(SELECT * FROM table_name WHERE... .). THEN INSERT INTO ... ELSE UPDATE SET ...Copy the code

6. Use method of cursor when sqlite3

Cursor is a cursor object. The cursor is a MySQLdb object that implements iterators (def__iter__()) and generators (yield). Len () and index() are supported only when fetchone() or fetchall() returns a tuple, which is why it is an iterator. The cursor can only be used once, that is, after each use, its position is recorded, and the next fetch is from the cursor instead of from the beginning. Moreover, after all data is fetched, the cursor is no longer useful, that is, it can no longer fetch data.

An introduction to the operating

import sqlite3
conn= sqlite3.connect('somedatabase.db') Create database
cu =conn.cursor() The cursor that can get the connection
Copy the code

Create table

cu.execute("""create table catalog ( id integer primary key, pid integer, name varchar(10) UNIQUE )""")
Copy the code

Insert two pieces of data

cu.execute("Insert into the catalog values (0, 0, 'name1')")
cu.execute("Insert into the catalog values (1, 0, 'name2)")
conn.commit()
Copy the code

Select

cu.execute("select * from catalog")
cu.fetchall()
[(0.0.'name1'), (1.0.'name2')]
cu.execute("select * from catalog where id = 1")
cu.fetchall()
[(1.0.'name2')]
Copy the code

Modify (update)

Cu. The execute (" update the catalogset name='name2 'where id = 0 ") cx.mit () cu.execute(" select * from catalog ") cu.fetchone()Copy the code

The output

(0.0, ‘name2′)
Copy the code

Delete

Cu. The execute (" deletefrom catalog where id= 1") cx.com MIT (cu). The execute (" select *fromThe catalog ") cu. Fetchall ()Copy the code

The output

[(0.0.'name2')]
Copy the code

Connect object methods:

The COMMIT method is always available, but it does nothing if the database does not support transactions. If a connection is closed but there are uncommitted transactions, they are implicitly rolled back – but only if the database supports rollback.

The ROLLBACK method may not be available because not all databases support transactions (which are a series of actions). If available, you can “undo” all uncommitted transactions.

The cursor method introduces us to another topic: cursor objects. Scan rows of SQL queries through cursors and check the results. Cursor connections support more methods and are probably better used in programs.

Cursor:

cu = conn.cursor()
Copy the code

A cursor that can obtain a connection and can be used to execute SQL queries.

conn.commit()
Copy the code

Make sure you commit after you’ve inserted and made some changes so that you can actually save those changes to the file.

Cursor object methods:

cu.fetchone()
Copy the code

Fetchall () returns all the data in the result set, resulting in a list of tuples. Each tuple element is arranged in the order of the table fields. Note that the cursor is stateful. It keeps track of the number of records that have been fetched, so you can generally traverse the result set only once. In the above case, fetchone() returns null. This should be noted during testing.

conn.close()
Copy the code

You can commit after every change to the database, rather than just when you are ready to close the data, using the close method.

7. Database connection object

When you call connect, specify the name of the database, open it if it exists, or create a new one if it doesn’t.

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

You can also create databases in memory.Copy the code

con = sqlite3.connect(":memory:")
Copy the code

The object returned when a database is opened, cx, is a database connection object that can do the following:Copy the code

Rollback ()– transaction rollback 3. Close ()– close a database connection 4. Cursor ()– Create a cursor about commit(), if isolation_level isolation level is default, You will need to use this command every time you operate on the database. You can also set isolation_level=None to automatic commit mode. Conn. isolation_level = None This is the transaction isolation level. By default, you need to commit to modify the database.

8. Use the cursor to query the database

We need to use the cursor object SQL statement to query the database and obtain the query object. To define a cursor, do the following.Copy the code

cu=cx.cursor()
Copy the code

Cursor objects have the following operations: Execute ()– execute SQL statement executemany– execute multiple SQL statements close()– close cursor fetchOne ()– fetch a record from the result, Fetchmany ()– fetchall()– Fetchall ()– Scroll ()– cursor scroll

9. Check whether the cursor result is empty

db = sqlite3.connect("test/user.db")
c=db.cursor()
cursor=c.execute("select info,flag,result from user where info='XXX'")
print(len(list(cursor)))
Copy the code

10.SQLite data type