Python SQLite simple tutorial, pythonsqlite

Python SQLite simple tutorial, pythonsqlite

I. Introduction to SQLite

SQLite is a lightweight database included in the C library. It does not require independent maintenance processes and allows SQL query statements of nonstandard variant to access the database. Some applications use SQLite to save internal data. It can also be used when building an application prototype for later migration to a larger database, such as PostgreSQL or Oracle.

The sqlite3 module, written by Gerhard häring, provides an SQL interface designed to comply with the DB-API 249 specification described by PEP 2.0.

2. Create and open a database

To use this module, you must first create a Connection object to represent the database. In the following example, the data is saved in the example. db file:
Copy codeThe Code is as follows: import sqlite3
Conn = sqlite3.connect ('example. db ')

If the specified database exists, the database will be opened directly; otherwise, the new database will be opened again and again.
You can also provide a special name: memory: to create a database in the memory.

Iii. Database Connection objects

Once you have a Connection object, you can create a Cursor object and call its execute () method to execute the SQL statement:

Copy codeThe Code is as follows: c = conn. cursor ()
 
# Create table
C.exe cute (''' create table stocks
(Date text, trans text, symbol text, qty real, price real )''')
 
# Insert a row of data
C.exe cute ("insert into stocks VALUES ('2017-01-05 ', 'buy', 'rhat', 2006, 100 )")
 
# Save (commit) the changes
Conn. commit ()
 
# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
Conn. close ()
 
The stored data is persistent and can be used for future access.

4. add, delete, modify, and query

1. create a table
Copy codeThe Code is as follows: c.exe cute ("create table catalog (id integer primary key, pid integer, name varchar (10) UNIQUE, nickname text NULL )")

The preceding statement creates a table named catalog, which has a primary key id, a pid, and a name and name that cannot be repeated, and a nickname is NULL by default.

2. DROP and TRUNCATE)
Copy codeThe Code is as follows: c.exe cute ("drop table catalog ")

The preceding statement deletes the catalog table.

In addition, SQLite does not clear the table, which is replaced by the following method:
Copy codeThe Code is as follows: c.exe cute ("delete from catalog ")

3. insert data and change (uptate) data

Python variables are usually used as values in SQL statements ). We do not recommend that you directly use python string operations to construct query statements, because this is insecure and your program is vulnerable to SQL injection attacks.

You can use the parameter substitution provided by the DB-API. Place '? 'As a placeholder, and then a tuples consisting of values are provided as the second parameter of the execute () method in the cursor (cursor. (Other database modules may use other placeholders, such as '% s' or': 1 ')
Copy codeThe Code is as follows:
# Larger example that inserts cannot records at a time
Purchases = [('2014-03-28 ', 'buy', 'ibm', 2006, 1000 ),
('2014-04-05 ', 'buy', 'msft', 2006, 1000 ),
('2014-04-06 ', 'clerk', 'ibm', 2006, 500 ),
]
C.exe cutees ('insert INTO stocks VALUES (?,?,?,?,?) ', Purchases)

C.exe cute ("UPDATE catalog SET trans = 'signature' WHERE symbol = 'ibm '")

4. Query (select) data

As mentioned above, we advocate the use of tuples for operations.
Copy codeThe Code is as follows: # Never do this -- insecure!
Symbol = 'rhat'
C.exe cute ("SELECT * FROM stocks WHERE symbol = '% S'" % symbol)

# Do this instead
T = ('rhat ',)
C.exe cute ('select * FROM stocks WHERE symbol =? ', T)
Print c. fetchone ()

5. delete data
Copy codeThe Code is as follows:
T = ('rhat ')
C.exe cute ("DELETE * FROM stocks WHERE symbol =? ", T)

For Python database operations on SQLite, how can I write a database query? (if the data exists, it will update the data. If the data does not exist, it will be written.

You must first read the data and then close the cursor. Otherwise, no data can be read.
In your dbfind function, you should query data after execute and then execute c. close (). Otherwise, no data can be read and errors will occur.

Python sqlite Problems

Because you want to insert a string, but according to your current writing method, there are no quotation marks on both sides, and the string must be enclosed by quotation marks.