Python Sqlite3 database operations

Source: Internet
Author: User

1. Connect to the database:

Cx = sqlite3.connect ('database. db'). cx is a database connection object and has the following operations:

Commit () -- transaction commit

Rollback () -- transaction rollback

Close () -- close a database connection

Cursor () -- create a cursor

 


2. Obtain the cursor object:

All SQL statements must be executed under the cursor object. cu = cx. cursor () is used to define a cursor. The cursor object has the following operations:

Execute () -- execute an SQL statement

Executemany -- execute multiple SQL statements

Close () -- close the cursor

Fetchone () -- get a record from the result

Fetchrecords () -- retrieve multiple records from the results

Fetchall () -- retrieve multiple records from the result

Scroll () -- cursor scroll

 


3. sqlite3 example:

3.1 create a table

Cu.exe cute ("" createtable catalog (

Idinteger primary key,

Pidinteger,

Namevarchar (10) UNIQUE )""")

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.

3.2 insert (insert) data

>>> Cu.exe cute ("insert into catalog values (?,?,?) ", (0, 0, 'name1 '))

>>> Cu.exe cute ("insert into catalog values (1, 0, 'Hello ')")

>>> Cx. commit ()

If you want to, you can always use the cu cursor object. Note that the transaction statement commit () or rollback () must be used to modify the data, and the object is the database connection object. cx is used here.

3.3 select data:

>>> Cu.exe cute ("select * from catalog ;")

>>> Cu. fetchall ()

[(0, 0, 'name2'), (1, 0, 'Hello')]

Fetchall () returns all the data in the result set, and the result is a list of tuple. Each tuple element is arranged in the order of fields in the table creation. Note that a cursor is stateful and records the first few records of the result. Therefore, you can traverse the result set only once. In the preceding case, if fetchone () is executed, null is returned. Note This during testing.

>>> Cu.exe cute ("select * from catalog where id = 1 ")

>>> Cu. fetchone ()

(1, 0, 'Hello ')

If no statement is modified for the database, you do not need to execute the transaction statement after execution.

3.4. update (modify) data:

>>> Cu.exe cute ("update catalog set name = 'name2' where id =? ", (1 ,))

>>> Cx. commit ()

>>> Cu.exe cute ("select * from catalog ")

>>> Cu. fetchone ()

(0, 0, 'name2 ')

3.5 delete data:

>>> Cu.exe cute ("delete from catalog where id =? ", (1 ,))

>>> Cx. commit ()

>>> Cu.exe cute ("select * from catalog ")

>>> Cu. fetchall ()

[(0, 0, 'name2')]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.