The Python DB-API

DRAFT, 8/24/01 451 Copyright © 2001 O’Reilly & Associates, Inc. 25 The Pyt DB-API is a sing MySQL impleme dustman.net/and Win32 installer, 1. Uncompress zip FILENAM 2. Change dire 3. Issue the co The MySQLdb m as well as severa with asterisks. Module: M The entry point The return value that you can use Module Attr Attribute: api Synopsis A string constanands gun- attributes re marked ) method. L database rts.ibutes level t storing the version of the DB-API that MySQLdb suppoySQLdb into the MySQL module is via the MySQLdb.connect( from this method represents a connection to a MySQ for all of your MySQL operations.Chapter 23 hon DB-API 0. le API for supporting database-independent database access. The ntation of this API, MySQLdb, can be downloaded from http:// y/python/MySQLdb. It comes with a Redhat RPM Linux installer, a and a Python script for other platforms. For the "other platforms": the .tar.gz file that contains MySQLdb using the comm E.tar.gz and tar xf FILENAME.tar. ctories into the newly generated MySQLdb directory. mmand: python setup.py install odule contains both the standard DB-API methods and l proprietary methods and attributes. Proprietary APIs a DRAFT, 8/24/01 Attribute: para Synopsis Defines the type ports many valid mat" and "pyform Attribute: quo Synopsis Maps Python typ Attribute: thre Synopsis Specifies the lev 0 - Threads may 1 - Threads may 2 - Threads may 3 - Threads may Attribute: type Synopsis Maps MySQL typ initialized with t { FIELD_TYPE FIELD_TYPE.S FIELD_TYPE.L FIELD_TYPE.F FIELD_TYPE.D FIELD_TYPE.L FIELD_TYPE.I FIELD_TYPE.Ymstyle of parameter placeholder in parameterized queries. DB-API sup- values for this attribute, but MySQLdb actually supports only "for- at". This attribute is largely meaningless to MySQL developers. te_conv* es to MySQL literals via a dictionary mapping. adsafety el of thread-safety supported by MySQLdb. Possible values are: no share the module share the module, but not the connections share the module and connections share the module, connections, and cursors _conv* es from strings to the desired mapping type using. This value is he following values: .TINY : int, HORT: int, ONG: long, LOAT: float, OUBLE: float, ONGLONG: long, NT24: int, EAR: int }Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Module Met Method: MySQ Signature connection = Synopsis Connects to the keyword/value p host The name o user The user ID MySQL to m passwd The passwo db The MySQL port Directs MyS left unspecif unix_socket Identifies the allows it. client_flags An integer s tion flags ar real_conn This method ret base. Example connection = hods L.connect( ) MySQL.connect(params) MySQL database engine represented by the various connection arameters. These parameters include: f the server on which the MySQL database is running to use for connecting to MySQL. This user should be allowed by ake the connection. rd to authenticate the user ID for the connection. database to which the application is attempting to connect. QLdb to connect to a MySQL installation on a custom part. When ied, the method will use the default MySQL port of 3306. location of a socket or named pipe to use if the value of the host pecifying the client connection flags to use. These client connec- e the same ones enumerated in Chapter 22, C API for the mysql_ ect() method. urns a Python object representing a connection to a MySQL data- MySQLdb.connect(host='carthage', user='test', passwd='test', db='test');Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Connection Attribute: db* Synopsis A window into t underlying C AP Connection Method: close( Signature close() Synopsis Closes the curr resources. Example connection = connection.c Method: comm Signature commit() Synopsis Commits the cur Example connection = connection._ cursor = con cursor.execu cursor.execu connection.c connection.c Attributes he MySQL C API. MySQLdb uses this attribute to make calls to the I. Methods ) ent connection to the database and releases any associated MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); lose(); it( ) rent transaction by sending a COMMIT to MySQL. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); transactional = 1; nection.cursor(); te("UPDATE TNAME SET COL = 1 WHERE PK = 2045"); te("UPDATE TNAME SET COL = 1 WHERE PK = 3200"); ommit(); lose();Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Method: curso Signature cursor = cur Synopsis Creates a cursor ments executed Example connection = cursor = con cursor.execu connection.c Method: rollba Signature rollback() Synopsis Rollsback any u transactional pro Example connection = connection._ cursor = con cursor.execu try: cursor.e connecti except: connecti connection.c Cursor Attri Attribute: arra Synopsis Specifies the nu call. By default, row at a time byr( ) sor() associated with this connection. Transactions involving any state- by the newly created cursor are governed by this connection. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); nection.cursor(); te("UPDATE TNAME SET COL = 1 WHERE PK = 2045"); lose(); ck( ) ncommitted statements. This only works if MySQL is setup for cessing in this context. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); transactional = 1; nection.cursor(); te("UPDATE TNAME SET COL = 1 WHERE PK = 2045"); xecute("UPDATE TNAME SET COL = 1 WHERE PK = 3200"); on.commit(); on.rollback(); lose(); butes ysize mber of rows to fetch at a time with the fetchmany() method this value is set to 1. In other words, fetchmany() fetches one default.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Attribute: desc Synopsis Describes a resu sequence conta internal_siz Attribute: row Synopsis Provides the num attribute is natur has been made o Cursor Meth Method: callpr Signature callproc(pro Synopsis This method is n Method: close( Signature close() Synopsis Closes the curs mingError wi Example cursor = con cursor.close Method: execu Signature cursor = exeription lt column as a read-only sequence of seven-item sequences. Each ins the following values: name, type_code, display_size, e, precision, scale, null_ok. count ber of rows returned through the last executeXXX() call. This ally read-only and has a value of -1 when no executeXXX() call r the last operation does not provide a row count. ods oc() cname [,parameters]) ot supported by MySQL. ) or explicitly. Once closed, a cursor will throw an Program- ll be thrown if any operation is attempted on the cursor. nection.cursor(); (); te() cute(sql [,parameters])Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Synopsis Sends arbitrary ized, then the o parameter value SQL can then be The parameters form multiple o DB-API 2.0 spec Example connection = cursor = con cursor.execu Method: execu Signature cursor.execu Synopsis Prepares a SQL ter sequences or Example connection = cursor = con cursor.execu Method: fetcha Signature rows = curso Synopsis Fetches all rema Example connection = cursor = con cursor.execu for row in cSQL to MySQL for execution. If the SQL specified is parameter- ptional second argument is a sequence or mapping containing s for the SQL. Any results or other information generated by the accessed through the cursor. of this method may also be lists of tuples to enable you to per- perations at once. This usage is considered depricated as of the ification. You should instead use the executemany() method. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); nection.cursor(); te(’SELECT * FROM TNAME’); temany() temany(sql,parameters) statement and sends it to MySQL for execution against all parame- mappings in the parameters sequence. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); nection.cursor(); temany("INSERT INTO COLOR ( COLOR, ABBREV ) VALUES (%s, %s )", (("BLUE", "BL"), ("PURPLE", "PPL"), ("ORANGE", "ORN"))); ll() r.fetchmany() ining rows of a query result as a sequence of sequences. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); nection.cursor(); te("SELECT * FROM TNAME"); ursor.fetchall():Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 # proces Method: fetchm Signature rows = curso Synopsis Fetches the next rows are availab If specified, the default value fo parameter is larg contain all rema Example connection = cursor = con cursor.execu rows = curso Method: fetcho Signature row = cursor Synopsis Fetches the next method will retu the SQL execute Example connection = cursor = con cursor.execu row = cursor print "Key: print "Value Method: inser Signature id = cursor.s row any() r.fetchmany([size]) set of rows of a result set as a sequence of sequences. If no more le, this method returns an empty sequence. size parameter dictates how many rows should be fetched. The r this parameter is the cursor’s arraysize value. If the size er than the number of rows left, then the resulting sequence will ining rows. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); nection.cursor(); te("SELECT * FROM TNAME"); r.fetchmany(5); ne() .fetchone() row of a result set returned by a query as a single sequence. This rn None when no more results exist. It will throw an error should d not be a query. MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); nection.cursor(); te("SELECT * FROM TNAME"); .fetchone(); ", row[0]; : ", row[1]; t_id()* insert_id()Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Synopsis Returns the last MENT field. Example connection = cursor = con cursor.execu id = cursor. Method: nexts Signature cursor.nexts Synopsis This method alw Method: setinp Signature cursor.setin Synopsis This method doe Method: setou Signature cursor.setou Synopsis This method doeinserted ID from the most recent INSERT on an AUTO_INCRE- MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'); nection.cursor(); te("INSERT INTO TNAME (COL) VALUES (1)"); insert_id(); et() et() ays returns None for MySQL. utsizes() putsizes(sizes) s nothing in MySQL. tputsize() tputsize(size [,column]) s nothing in MySQL.Copyright © 2001 O’Reilly & Associates, Inc.