Use Python to operate SQLite databases and pythonsqlite Databases

Source: Internet
Author: User

Use Python to operate SQLite databases and pythonsqlite Databases
Connect to database

From version 2.5, the Python standard library has a sqlite3 module dedicated for SQLite. The connection to the SQLite database is as follows:

import sqlite3 as dbapicon = dbapi.connect('population.db')cur = con.cursor()

The first statement is used to reference the database API;
The second statement creates a connection to the database: Call the connect Method of the database module. The parameter of this method is a string that defines the database to be connected. Since SQLite stores the entire database in a file on the disk, This is the filePath. If the database does not exist, it will be created directly;
The third statement is used to obtain a cursor. It is similar to the cursor in the text editor and is used to record our current location in the database. When multiple programs access the database at the same time, the database knows who is doing what.

Data Type comparison

SQLite Python Description
NULL NontType I don't know.
INTEGER Int or long Integer
REAL Float 8-byte floating point number
TEXT Unicode or str String
BLOB Buffer Binary data (Binary Large OBject)
Database Operations

Now we can operate the database. The SQL statement we want to execute is put into a string and the database is told to execute this string, as shown below:

cur.execute('CREATE TABLE Student(Stuid TEXT, Age INTERGER, Name TEXT)')cur.execute('INSERT INTO Student VALUES("00001", 20, "Lucy")')cur.execute('INSERT INTO Student VALUES("00002", 21, "Lily")')

After inserting the database into the database or making any other changes to the database, we must submit the changes through the commit method of the connection:

con.commit()

Now we can use the following method to obtain data:

cur.execute('SELECT * FROM Student')print(cur.fetchone())print(cur.fetchall())

The fetchone method usesTuplesReturns each record, where each element is arranged in the order specified by the query. If no other record exists, None is returned. By default, the TEXT in the database is returned as a Unicode string. We can tell sqlite3 to return a string of the str type: Set the text_factory Member of the connection to the str type.

con.text_factory = str

The function of the fetchall method isTuplesReturns all data generated by the query.

A quick insert method:

ss = [("00003", 20, "David"), ("00004", 23, "Cneagle"),      ("00005", 22, "qxzy")]for s in ss:    cur.execute('INSERT INTO Student VALUES(?, ?, ?)', (s[0], s[1], s[2]))con.commit()

Two parameters are used in this execute call. The first is an SQL statement with question marks. These question marks are placeholders of the values to be inserted, and the second is a tuple consisting of values to be inserted, when executing this statement, the database replaces the question marks with these values from left to right.

Note:

  • The Data Types of SQL are not exactly the same as those in programming languages. Therefore, it is necessary to convert the data types between them when writing applications.
  • The modifications made to the database will not have any effect until they are submitted. This ensures that the database is in the same state when it is operated by more than two programs at the same time.

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.