The database's operation has become very easy to use in Python, with a set of API standards. The following is how to use this frame definition. This framework contains the following
Module Interface Connection Object Cursor Object DBI Auxiliary Object Data Type and Definition How to Implement Tips from 1.0 to 2.0
Module interface
The parameter format of Connect (parameters ...) is as follows:
DSN data source name
User username (optional)
Password password (optional)
Host host name (optional)
Database database name (optional)
for example:
Connect (DSN = 'myhost: mydb', user = 'guido', password = '234 $')
or
Connect ('218.244.20.22', 'username', 'password', 'DatabaseName')
This standard specifies some global variables:
Apilevel:
Represents the version of the DB-API, points '1.0' and '2.0'. If there is no definition, the default is '1.0'
Threadsafety:
0 Threads May Not Share The Module.
1 Threads May Share The Module, But Not Connections.
2 Threads May Share The Module and connections.
3 Threads May Share The Module, Connections and Cursors.
Paramstyle:
Used to indicate the transfer method of the parameter, divided into the following five types:
'QMark' Question mark identity style. E.G '... where name =?'
'numeric' number, placeholder style. E.G '... where name =: 1'
'named' naming style. E.G 'Where name =: name'
'Format' ANSI C Printf style. E.G '... where name =% s'
'Pyformat' Python Expansion Indication. E.G '... where name =% (Name) s'
Abnormal class:
Standarderror
| __WARNING
| __ERROR
| __Interfaceerror
| __DatabaseError
| __DataError
| __OPERATIONALROR
| __IntegerityError
| __Internalrror
| __ProgramMingerror
| __NOTSupportedError
Connection object
The connection object contains the following method:
.close ()
Shut down connection
.commit ()
Submission operations in transaction processing
. ROLLBACK ()
Rollback operation inside transaction processing
.cursor ()
Get a cursor
Cursor object
The cursor object contains the following properties and methods:
.description
A list (name, type_code, display, scale, null_ok) This property is only after the data is obtained, otherwise it will be NULL value.
.rowcount
Represents the number of rows of the return value. If you do not execute an executexxxx () method or this module does not implement this method, you will return -1
.callproc (procname [, parameters])
(This is an optional method, it should be not all databases to support stored procedures)
.close ()
Close a cursor
.execute (Operation [, Parameters]) Prepare and execute a database operation (including queries and commands)
.executemany (Operation, SEQ_OF_PARAMETERS)
Prepare a database command, then execute multiple commands according to the parameter
Fetchone ()
Returns the query result of the first line
.fetchmany ([size = cursor.araysize])
Returns the value specified for multiple rows
Fetchall ()
Returns all query results
.arraysize
This parameter value represents the number of rows obtained by FetchMany by default
Data type and definition
Define some commonly used data types. But now you can't use it, just don't analyze
Note
Of course, we have to know, this is just a standard, which generally defines the implementation of the standard, but there are still many specific implementations, we also need to master what is, if we master these standards, Then there will be no problem.
Here are several databases related to related URLs.
Database Topic Guide
Python's database uses the wizard, there is quite good information, including API definitions, drive linkages, etc.
MSSQL driver
Is MSSQL driver
example
The following example is that MSSQL is a template, but it can be done by other drivers. This is similar to the Perl database operation, which can make us easier for the portation between different databases.
Query data
Import MSSQL
DB = mssql.connect ('SQL Server IP', 'Username', 'Password', 'DB_NAME')
c = db.cursor ()
SQL = 'SELECT TOP 20 RTRIM (IP), RTRIM (DNS) from Detail'
C.EXECUTE (SQL)
For f in C.FetChall ():
Print "IP IS% S, DNS IS% S"% (f [0], f [1])
2. Insert data
SQL = 'INSERT INTO DETAIL VALUES (' 192.168.0.1 ',' www.dns.com.cn ')
C.EXECUTE (SQL)
3. An example of ODBC
Import dbi, ODBC # odbc modules
Import Time # Standard Time Module
DBC = ODBC.ODBC (# Open A Database Connection
'Sample / Monty / Spam' # 'Datasource / User / Password'
)
CRSR = dbc.cursor () # Create a CURSOR
Crsr.execute (# Execute Some SQL)
"" "
Select Country_ID, Name, Insert_change_date
From country
ORDER BY Name
"" "
)
Print 'Column Descriptions:' # s olumn descriptions
For COL IN CRSR.DESCRIPTION:
Print '', col
Result = crsr.Fetchall () # fetch the results all at ONCE
Print '/ Nfirst Result Row: / N', Result [0] # Show flight ROW
Print '/ NDATE CONVERSIONS:' # Play with dbidate Objectdate = Result [0] [- 1]
FMT = '% -25s% -20s'
PRINT FMT% ('Standard String:', Str (Date)
Print FMT% ('Seconds Since:', Float (Date))
TimeEtuple = Time.localTime (Date)
Print FMT% ('Time Tuple:', TimeTuple)
Print FMT% ('User Defined:', Time.StrFTIME ('% D% B% Y', TIMETUETUE))
------------------------------- Output -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------
Column Descriptions:
('country_id', 'Number', 12, 10, 10, 0, 0)
('Name', 'String', 45, 45, 0, 0, 0)
('INSERT_CHANGE_DATE', 'DATE', 19, 19, 0, 0, 1)
First RESULT ROW:
(24L, 'Argentina',
Date conversions:
STANDARD STRING: Fri Dec 19 01:51:53 1997
Seconds Since Epoch: 882517913.0
Time Tuple: (1997, 12, 19, 1, 51, 53, 4, 353, 0)
User Defined: 19 December 1997
Back to this page
