Python sqlite3 Database operations

Python sqlite3 Database operations

SQLite3 is a python built-in module that is a very small embedded open source database software.

1. Import the Python SQLite database module

Import Sqlite3

　　

2. Python sqlite3 module API

"" "Sqlite3.connect (Database [, timeout, other optional arguments]) The API opens a link to the SQLite database file. You can use ": Memory:" To open a database connection in RAM instead of on disk. If the database opens successfully, a connection object is returned. When a database is accessed by multiple connections, and one of them modifies the database, the SQLite database is locked until the transaction commits. The timeout parameter indicates the duration of the connection waiting for a lock until an abnormally disconnected connection occurs. The timeout parameter defaults to 5.0 (5 seconds). If the given database name filename does not exist, the call will create a database. If you do not want to create a database in the current directory, you can specify a file name with a path so that you can create the database anywhere. Connection.cursor ([Cursorclass]) This routine creates a cursor that will be used in Python database programming. The method accepts a single optional parameter, Cursorclass. If this argument is provided, it must be an extended self-sqlite3. The cursor's custom cursor class. Cursor.execute (SQL [, optional parameters]) The routine executes an SQL statement. The SQL statement can be parameterized (that is, using placeholders instead of SQL text). The Sqlite3 module supports two types of placeholders: Question Marks and named placeholders (named Styles). Example: Cursor.execute ("INSERT into people values (?,?)", (who, age)) Connection.Execute (SQL [, optional parameters]) The routine is a shortcut to the method provided by the cursor object above, which creates an intermediate cursor object by invoking the cursor method, and then invokes the Execute method of the cursor with the given parameters. Cursor.executemany (SQL, seq_of_parameters) This routine executes an SQL command on all parameters or mappings in Seq_of_parameters. Connection.executemany (sql[, parameters]) The routine is an intermediate light created by the call cursor methodA shortcut to the object, and then invokes the cursor's Executemany method by the given argument. Cursor.executescript (Sql_script) Once the routine receives the script, it executes multiple SQL statements. It executes the COMMIT statement first, and then executes the SQL script passed in as a parameter. All SQL statements should be separated by semicolons (;). Connection.executescript (Sql_script) The routine is a shortcut to the middle cursor object created by the call cursor method and then calls the cursor's Executescript method by the given argument. Connection.total_changes () This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. Connection.commit () The method submits the current transaction. If you do not call this method, any actions you have made since you last called commit () are not visible to other database connections. Connection.rollback () The method rolls back the changes made to the database since the last call to commit (). Connection.close () This method closes the database connection. Note that this does not automatically invoke commit (). If you have not called the commit () method before, close the database connection directly, and all changes you have made will be lost! Cursor.fetchone () This method gets the next row in the query result set, returns a single sequence, and returns none when no more data is available. Cursor.fetchmany ([size=cursor.arraysize]) This method gets the next row of groups in the query result set, returning a list. When there are no more rows available, an empty list is returned. The method attempts to get as many rows as are specified by the size parameter. Cursor.fetchall () The routine gets all (the remaining) rows in the query result set, returning a list. When no rows are available, an empty list is returned. """

　　

3. Create/Open Sqlite3 database
When calling the Connect function, specify the name of the library, if the specified database exists, open the database directly, and if it does not exist, create a new one and open it.

con = sqlite3.connect ("sql.db")

　　

Creating the database in memory

con = Sqlite3.connect (": Memory:")

　　

4. Database Connection objects
The object that is returned when the database is opened con is a database connection object, with the following actions:

1). Commit ()--transaction commit 2). Rollback ()--transaction rollback 3). Close ()--Closes database connection 4). Cursor ()--Create a cursor

Note: With respect to commit (), if the Isolation_level isolation level defaults, the command is required for each operation of the database and can be set isolation_level=none so that it becomes autocommit mode

5. Querying the database with cursors
Query the database using the Cursor object SQL statement to obtain the query object. Define a cursor by using the following method:

CU = Con.cursor ()

　　

The specific actions of cursors are:

1). Execute ()-Execute SQL statement 2). Executemany ()-Executes multiple SQL statement 3). Close ()--closes cursor 4). Fetchone ()--Remove a record from the result and point the cursor to the next record 5). Fetchmany ()--take multiple records from the result 6). Fetchall ()--Remove all records from the results 7). Scroll ()--cursor scrolling

　　

(1). CREATE TABLE

Cu.execute ("CREATE TABLE MyTable (ID integer primary key, PID integer,name varchar (Ten) unique,nickname text NULL)")

　　

(2). Inserting data

Cu.execute ("INSERT INFO Company" (id,name,age,address,salary) VALUES (1, ' Paul ', +, ' California ', 20000.00) ") Con.commit () # Submit the inserted data

　　

(3). Querying data

Cu.execute ("SELECT * FROM MyTable")

　　

(4). modifying data

Cu.execute ("Update mytable set name= ' XSS ' where id = 0") Con.commit ()

　　

(5). Delete Data

Cu.execute ("Delete from mytable where id = 1") Con.commit ()

　　

(6). Use Chinese
Make sure your IDE or system default encoding is Utf-8, and add u before Chinese

x = U ' Chinese ' cu.execute ("Update mytable set name=? WHERE id = 0 ", x)

　　

Python sqlite3 Database operations