Turn: Pysqlite notes

This is an old note, originally placed on the old blog, and recently because of a small company inside, want to use simple database to store data, think of using SQLite to do, online search some tutorials. Unexpectedly found that the original more than a year ago, I also learned a while, unfortunately because not commonly used, and now has basically forgotten the light, plus their database has not always been very familiar with, on the new blog, by the Way review.

The main content of this note comes from the sqlite3 of Python using a concise and pysqlite tutorial.

SQLite This small database, often seen in Firefox or other software, it seems that there is no independent maintenance process, but the data are stored in a separate file. The internet searched the information, looked at the Python pysqlite module, it is python in the package implementation of SQLite.

Database operations

Python's database module has a unified interface standard, so the database operations are basically unified, basically divided into the following steps (assuming the database module is db):

1. Create a database connection with Db.connect (), and the Connection object is conn.
2. If you do not need to return the query results, call Conn.execute () directly.
3. If you need to return query results, you need to first create a cursor object cur through conn.cursor () and get the results of the query using functions such as Cur.fetchone ().
4. Depending on the database isolation level, after you modify the database, you may need to commit the transaction manually using Conn.commit ().
5. Call the corresponding close () method to close CUR and conn.

A more detailed database operations API can refer to the official Python documentation: click here.

Pysqlite Basic Operation

1. First import the Sqlite3 module

Import sqlite3

2. Then create a database link

= sqlite3.  Connect(' test.db ')     

where "Test.db" is the name of the database and is created automatically if the database file does not exist; otherwise, open the specified database file and create a database connection object that has the following main actions:

• Commit (): Transaction commit
• Rollback (): Transaction rollback
• Close (): Close a database connection
• Cursor (): Create a cursor

where the commit () method is used to commit the transaction, the rollback () method is used to roll back to the place where the commit () method was last called. You can Connection.isolation_level define the transaction isolation level, and when the property is set to none, it commits the transaction automatically and does not require the commit () method to be called explicitly.

In addition to specifying the database files directly, there is another way to create a database in memory. The method is to pass ": Memory:" As a parameter to the Sqlite.connect () function:

= sqlite3.  Connect(": Memory:")     

3. Next you need to create a cursor object

= Conn.  Cursor()   

Find a specific explanation of the cursor on the Web (source):

Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting one record at a time from a result set that includes multiple data records. Cursors are always associated with an SQL selection statement. Because a cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position that points to a particular record in the result set. When you decide to process a result set, you must declare a cursor that points to the result set.

Cursor objects mainly include the following methods:

• Execute (): Execute SQL statement
• Executemany (): Execute multiple SQL statements
• Close (): Close cursor
• Fetchone (): Take a record from the result
• Fetchmany (): Take multiple records from the result
• Fetchall (): Remove all records from the results

Note: In order to improve programming efficiency, the connection object in Pysqlite also contains functions such as execute (), which can be avoided by using these functions to avoid creating cursor objects, as described in Pysqlite's documentation: Click here for the following example from the previous document, Describes how to use these methods:

ImportSqlite3persons= [ ("Hugo", "Boss"), ("Calvin", "Klein") ]Con=Sqlite3.Connect(": Memory:")# Create The tableCon.Execute("CREATE table person (FirstName, LastName)")# Fill The table (use the placeholder format provided by Pysqlite to improve security)Con.Executemany("INSERT into person (firstname, LastName) VALUES (?,?)",Persons)# Print the table contents (using iterative methods to get query results) # con.execute (..) method to return a cursor object and avoid manually creating a cursor object. for row in Conexecute ( "SELECT FirstName, LastName from"  print Rowprint  "I just deleted" , Con.execute ( "delete from person"  Rowcount,  "rows"       

4. Execute an SQL statement using the Execute () method

SQLite SQL statements in the syntax format, refer here.

For example, create a database table by using creating table:

Cur.  Execute("' CREATE TABLE Stocks (date Text,trans text,symbol text,qty real,price Real) ')   

After the table is created, you can insert the data using the INSERT statement:

Cur.  Execute("" "INSERT into stocks values (' 2006-01-05 ', ' BUY ', ' rhat ', 100,35.14)   " ") 

5. Commit a transaction

Conn.  Commit()  

6. Close cursors and database links to release resources

Cur.  Close()conn.  Close()      

Pysqlite Other operations

The variables defined in the script often need to be used when executing the INSERT statement, and there are many forms of placeholders available in Pysqlite, which makes it easy to replace variables and improve security.

For example, go back to the steps you inserted earlier:

Cur.  Execute("" "INSERT into stocks values (' 2006-01-05 ', ' BUY ', ' rhat ', 100,35.14)   " ") 

Here, using the form of a string SQL statement, if we need to change some data, we have to write it again, so reusability is greatly reduced. As a result, Python provides a mechanism to use variables in Python instead of specific placeholders in SQL string statements. While the specific syntax can be accessed by querying the Paramstyle parameter of the DB module, here are several types of syntax descriptions:

' Qmark ': Question mark style,          e.g. WHERE name=? ' Numeric ': Numeric, positional style,            e.g. WHERE name=:1 ' named ': named Style,          e.g. WHERE name=:name ' format ': ANSI C printf format codes,           e.g. WHERE name=%s ' Pyformat ': Python extended format codes,           e.g. WHERE name=% (name) s '

For information on how to use this blog, you can refer to the Python database query parameter style. Of course, Python's own documentation also tells you how to use it, or refer to it.

Currently Pysqlite supports two forms of Qmark and named. For example, use the Qmark form and pass it as a tuple (tuple) to the Execute function as the second parameter:

=(' 2006-01-05 ',' BUY ',' Rhat ','35.14 ')cur.  Execute("INSERT into stocks values (?,?,?,?,?)" , t)                  

This approach is much more convenient when more data is needed:

ForTInch [(' 2006-03-28 ', ' BUY ', ' IBM ', 1000, 45.00), (' 2006-04-05 ', ' BUY ',  ' msoft ' ,  1000, 72.00  ( ' 2006-04-06 ' ,  ' SELL ' ,  ' IBM ' ,  500, 53.00)]: Curexecute ( ' INSERT into stocks values (?,?,?,?,?) ' , T)           

Alternatively, you can use the Executemany () method to implement the second parameter of the method, which is a sequence or an iterator, followed by the SQL statement specified by the first parameter to replace execution. The above insert example can be written as:

Param_seq= [    (' 2006-03-28 ', ' BUY ', ' IBM ', 1000, 45.00), (' 2006-04-05 ', ' BUY ',  ' msoft ' , 1000, 72.00  (,  ' SELL ' ,  ' IBM ' , 500 53.00) ]. Executemany ( ' INSERT into stocks values (?,?,?,?,?) ' , Param_seq)         

Then there is a need to execute multiple SQL statements at once, just like typing multiple SQL statements in some GUI of MySQL, then clicking on Execute, in Pysqlite, you can do this task by Executescript () method. For example (examples are from official documents):

Cur.  Executescript ("" "    CREATE TABLE person (        FirstName,        LastName,        age    );    CREATE TABLE book (        title,        author,        published    );    Insert into book (title, author, published)    values (        ' Dirk gently ' s holistic Detective Agency ',        ' Douglas Adams ',        1987    ); ""    )

SQLite notes

1. How to determine if a table exists in the database
In SQLite there is a special table Sqlite_master, which is read-only, and the table definition statement is as follows:

(  Type text,  Name text, tbl_name text, rootpage INTEGER, SQL text) ;

Where, for table, the type value is "table", and name is the table name of the database. So querying all the tables in the database, you can use the following method:

SELECT name from Sqlite_masterwhere type=' table 'ORDER by name;   

The corresponding if, to find out if table stocks can do this:

SELECT Count(*)where name=' stocks 'and type=' table ';    

The return 1 represents the presence, and 0 indicates that it does not exist.

Reference: Http://sqlite.org/faq.html#q7

Turn: Pysqlite notes