Python Database Operations Manual

Data | The operation of database database has become very useful in python today, with a set of API standards. Here's how to use this set of framework definitions. This framework contains the following partial module interface connection object cursor object dbi Secondary object data type and hints on how to implement it From 1.0 to 2.0 the Change example module interface
Connect (Parameters ...) the parameter format is as follows:

DSN data Source name user username (optional) password password (optional) host hostname (optional) database name (optional) Example: Connect (dsn= ' myhost:mydb ', user= ' Guido ', password= ' 234$ ') or connect (' 218.244.20.22 ', ' username ', ' password ', ' DatabaseName ')

This standard sets out some of the following global variables:

APILevel:

Represents the Db-api version, divided into ' 1.0 ' and ' 2.0 '. If not defined, the default is ' 1.0 '

Threadsafety:

0 Threads may isn't 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 represent the parameters of the delivery method, divided into the following five kinds: ' Qmark ' question mark identification style. e.g ' ... WHERE name=? ' Numeric ' numbers, 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 expands the notation. e.g ' ... WHERE name=% (name) s '

Exception class:

Standarderror|__warning|__error |__interfaceerror |__databaseerror |__dataerror |__OperationalError |__ Integerityerror |__internalerror |__programmingerror |__notsupportederror
Connection objects
The Connection object contains the following methods:
. Close () Closes the connection. Commit () is used for the commit operation inside the transaction. Rollback () is used for rollback operations inside transactions. Cursor () Gets a cursor cursor object
The cursor object contains the following properties and methods:
. Description a list (NAME,TYPE_CODE,DISPLAY_SIZE,INTERNAL_SIZE,PRECISION,SCALE,NULL_OK) This property is only after the data has been obtained, or it is a null value. RowCount represents the number of rows to return a value. If the Executexxx () method is not executed or the module does not implement this method, it returns-1 Callproc (procname[,parameters)) (This is an optional method, should not support stored procedures for all databases. Close () closes the cursor. Execute (operation[,parameters]) prepares and executes a database operation (including queries and commands). Executemany (Operation,seq _of_parameters) prepares a database command and executes multiple commands based on the parameters. Fetchone () returns the query results for the first row. Fetchmany ([size=cursor.arraysize]) returns the value of a specified number of rows. Fetchall () returns all query results. ArraySize This parameter value represents the number of data types and definitions that are commonly used to define the row numbers that are obtained by default Fetchmany. But not at this time, without analyzing the notes
Of course, what we need to know is that this is just a standard, generally defined within the standard will be implemented, but there are many specific implementations, we also need to grasp what things, but if we have these standards, then the operation of the general will not have a problem.

A few database-related URLs are given below
Database Topic Guide python databases use wizards, there are pretty good data, including API definition, drive coupling and so on MSSQL driver is MSSQL driver example
The example below is a sample of MSSQL, but it can be done with other drivers, and this is similar to Perl's database operations, allowing us to easily implement porting between different databases.

1. Query data

Import mssqldb = Mssql.connect (' sql Server IP ', ' username ', ' password ', ' db_name ') c = db.cursor () SQL = ' Select top Rtri M (IP), RTrim (DNS) from detail ' C.execute (SQL) to F in C.fetchall (): print "IP Yes%s, DNS is%s"% (F[0], f[1])

2. Inserting 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 Modulesimport time # Standard Time MODULEDBC = ODBC.ODBC (# Open a database connection ' Sample/mon Ty/spam ' # ' Datasource/user/password ') CRSR = Dbc.cursor () # Create a cursorcrsr.execute (# Execute some SQL "" "Select Co untry_id, name, insert_change_date from country order by name "" "") print ' column descriptions: ' # show Column descriptions For col in Crsr.description:print ", Colresult = Crsr.fetchall () # Fetch the results all at Onceprint ' \nfirst ' r Ow:\n ', result[0] # show the Rowprint ' \ndate conversions: ' # play with dbidate objectdate = RESULT[0][-1]FMT = '%-25s%-20s ' Print fmt% (' Standard string: ', str (date)) Print Fmt% (' seconds since epoch: ', float (date)) Timetuple = time. LocalTime (date) Print fmt% (' time tuple: ', timetuple) print fmt% (' User defined: ', time.strftime ('%d%B%Y ', timetuple))-- -----------------------------Output--------------------------------Column descriptions: (' country_id ', ' number ', 0, 0) (' name ', ' StrinG ',,, 0, 0, 0 (' insert_change_date ', ' Date ', ', ', 0, 0, 1)----(24L, ' Argentina ', <dbidate objec T at 7f1c80>) Date Conversions:standard String:fri Dec 01:51:53 1997 seconds since epoch:882517913.0 time tuple: ( 1997, 1, 4, 353, 0) user defined:19 December 1997

Back to this column home