Basic Python Tutorial _ Learning Note 22: Database support

Database supports PythonDatabaseAPI

There are many available databases that support the SQL standard, most of which have corresponding client modules in Python.

Global variables

module features of the python DB API

Variable name	Use
APILevel	The Python DB API version used
Threadsafety	Thread safety level of the module
Paramstyle	parameter styles used in SQL queries

Abnormal

Abnormal	Super class	Describe
StandardError		Generic base class for all exceptions
Warning	StandardError	Thrown when a non-fatal error occurs
Error	StandardError	Generic superclass for all error conditions
Interfaceerror	Error	Errors with respect to interfaces rather than databases
Databaseerror	Error	base class for database-related errors
DataError	Databaseerror	Database-related issues, such as value out of range
Operationalerror	Databaseerror	Database Internal Operation error
Integrityerror	Databaseerror	Relationship integrity is affected, such as key check failure
Internalerror	Databaseerror	Database internal errors, such as illegal cursors
Programmingerror	Databaseerror	User compilation errors, such as a table not found
Notsupportederror	Databaseerror	Request unsupported features (such as rollback)

in order to use the underlying database system, you must first connect to it. you need to use the connect function with the proper name , which has multiple parameters, and which parameter depends on the database.

The Connect function is a common parameter

Parameters	Describe	is optional
Dsn	The data source name, given that the parameter represents the database dependency	Whether
User	User name	Is
Password	User password	Is
Host	Host Name	Is
Database	Database name	Is

Connection Object Methods

Method name	Describe
Close ()	The connection object and its cursors are not available after the connection is closed
Commit ()	Commit a pending transaction if supported, otherwise do nothing
Rollback ()	Rollback of a pending transaction (may not be available)
Cursor ()	Returns the connected Cursor object

the rollback method may not be available, because not all databases support transactions.

The Commit method is always available, but it has no effect if the database does not support transactions. If the connection is closed, but there are uncommitted transactions, they are implicitly rolled back -but only when the database supports rollback.

The cursor method introduces another topic: a cursor object . The cursor executes the SQL Query and examines the results, and cursors support more methods than connections and may be better used in the program.

Cursor Object Methods

Name	Describe
Callproc (Name[,params])	Invokes a named database program with the given name and parameters (optional)
Close ()	Cursors are not available after a cursor is closed
Execute (oper[,params])	Perform SQL operations that may use parameters
Execute (OPER,PSEQ)	Perform SQL operations on each parameter in a sequence
Fetchone ()	Save the next row in the result set of the query as a sequence or None
Fetchmany ([size])	Get multiple rows in a query result set, default size is arraysize
Fetchall ()	The sequence of all (remaining) rows as sequences
Nextset ()	Jumps to the next available result set (optional)
Setinputsizes (sizes)	Pre-defining memory areas for parameters
Setoutputsize (Size[,col])	To set the buffer size for the obtained large data value

Cursor Object Properties

Name	Describe
Description	The resulting column describes the sequence, read-only
RowCount	Number of rows in the result, read-only
ArraySize	the number of rows returned in Fetchmany, which defaults to 1

Type

The database has different requirements for inserting values into columns of a certain type, in order to be able to interoperate correctly with the underlying SQL database, which defines constructors and constants for special types and values (Singleton mode).

DB API Constructors and special values

Name	Describe
Date (Year,month,day)	Create an object that holds a date value
Time (Hour,minute,second)	Create an object that holds a time value
Timestamp (Y,mon,d,h,min,s)	Create an object that holds a timestamp value
Datefromticks (Ticks)	Create an object that holds the number of seconds since the new era
Timefromticks (Ticks)	Create an object that holds a time value from a number of seconds
Timestampfromticks (Ticks)	Create an object that holds a timestamp value from a number of seconds
Binary (String)	To create an object that holds a binary string value
STRING	Describe string-based column types
BINARY	Describe binary columns
Number	Describe numeric columns
Datetime	Describe date / time Columns
ROWID	Description row ID column