Python Common libraries

Sqlite3

1. Create a Connection object to represent a db with the following code:

   Import= sqlite3.connect ('example.db')# If you want to create a DB in RAM, You can use a specific name: Memory:

   
   when there are multiple connection accessing the DB simultaneously and there is a process that modifies the DB, The DB is always locked until transaction is committed, and the common parameters of Connect () are:
   • timeout parameter specifies how long the connection waits and then throws an exception, The default is 5 seconds.
   • isolation_level: Set how to turn on Tranction, open transaction when a DML statement is encountered by default, submit transaction
   • cached_statements: This time connection the number of statements that can be held in the cache, by default,
   • uri: Boolean, specifying the DB with a URI, you can specify the option to connect, such as read-only open db
     db = Sqlite3.connect (' File:path/to/database?mode=ro ', uri=true)
2. Create a Cursor object to execute SQL commands, pay attention to the security of the SQL statements, and Execute,executemany and Executescript functions can be called directly using the Connection object. The cursor is automatically created inside the function and the cursor object is returned

   C=conn.cursor ()#never do this--insecure!symbol='Rhat'C.execute ("SELECT * FROM stocks WHERE symbol = '%s '"%symbol)#do this instead, notice that there is only one element in the tuple followed by a comma#What are the substitutions in the SQL statement?T= ('Rhat',) C.execute ('SELECT * FROM stocks WHERE symbol=?', T)Print(C.fetchone ())#Larger example that inserts many records at a timePurchases= [('2006-03-28','BUY','IBM', 1000, 45.00), ('2006-04-05','BUY','MSFT', 1000, 72.00), ('2006-04-06','SELL','IBM', 500, 53.00),] C.executemany ('INSERT into Stocks VALUES (?,?,?,?,?)', purchases)

4. If you finish executing the query statement using execute, you can use the following methods to get the data:
   • Use the cursor as a iterator to get one row of row data at a time using the For statement
   • Use Fetchone () to fetch one row of row data at a time, and finally return none
   • use Fetchall () to get the list of row, no data to return none
5. Adding a Python type that is not supported by SQLite to SQLite is the first to convert the type to a type supported by SQLite, usually with Str. There are two ways to do this:
   • implementation class __conform__ method

      __conform__  import  sqlite3  Class  point: def  __init__ (self, x, y): self.x, self.y = x, y def  __conform__ (Self, Protocol): #注意protocol if protocol is sqlite3. Prepareprotocol: return  "%f;%f "% (self . x, self.y) con = sqlite3.connect (":memory: ") cur = con.cursor () p = Point (4.0,-3.2) Cur.execute ("select? ", (p,)) print  ( Cur.fetchone () [0])  

   • use Sqlite3.register_ Adapter registers a adapter method for the class, converting the type to Str,sqlite3 module internally for Python's built-in type Datetime.date and Datetime.datetime provides adapter, Convert them to ISO.

     Adapter  Import sqlite3import datetimeimporttimedef adapt_datetime (ts):    returntime. Mktime (Ts.timetuple ()) Sqlite3.register_adapter (Datetime.datetime, Adapt_ datetime) con = Sqlite3.connect (": Memory:") cur = con.cursor () now = Datetime.datetime.now () cur.execute (" select?", (now,))print(Cur.fetchone () [0])

     
     You can also use Sqlite3.register_converter to register a convert method for a class to convert the values in the database to a Python type. To make Sqlite3 aware that the actual type of data queried from the database needs to be set to the Detect_types parameter of the Sqlite.connect method, there are two values:
     • by declared type (implicit method)
     • By Column name (display method)

       Detect_typeImportSqlite3classPoint:def__init__ (self, x, y): self.x, self.y = x, ydef__repr__ (self):return"(%f;%f)"% (self.x, SELF.Y)defAdapt_point (point):return("%f;%f"% (Point.x, point.y)).encode(' ASCII ')defConvert_point (s): x, y = list (map (float, S.Split(b ";")))returnPoint (x, y) # Register the Adaptersqlite3.register_adapter (point, Adapt_point) # Register the Convertersqlite3.register_ Converter (" Point", convert_point) p = Point (4.0, -3.2) ########################## 1) Using declaredTypescon = Sqlite3.connect (": Memory:", Detect_types=sqlite3. parse_decltypes) cur = con.cursor () cur.execute ("CREATE TABLE Test (P point)") Cur.execute ("INSERT INTO Test (p) VALUES (?)", (P,)) Cur.execute ("select p from Test")Print("With declared types:", Cur.fetchone () [0]) cur.Close() con.Close() ######################## 1) Using column Namescon = Sqlite3.connect (": Memory:", Detect_types=sqlite3. parse_colnames) cur = con.cursor () cur.execute ("CREATE TABLE Test (p)") Cur.execute ("INSERT INTO Test (p) VALUES (?)", (P,)) Cur.execute (' Select P as")p [Point]"From Test")Print("With column names:", Cur.fetchone () [0]) cur.Close() con.Close()

7. class Sqlite3. Row: Represents a row of data that can be used to access a column's value using a column name or index subscript
   
   

Python Common library