MySQL adapter
The main difference with the pure-Python module is the syntax to identify a database and a table, and the need to specify field types when creating a table
For compliance with MySQL vocabulary, the module defines three classes, Connection, Database and Table
Connection
Connection(host,login,password[,charset]) returns a connection to the MySQL server. If the optional parameter charset is provided, the TEXT fields must bet inserted as Unicode strings, or as their encoding with this charset ; the values read from the database will also be Unicode strings
Instances of Connection are dictionary-like objects : the keys are the database names and values are the matching instances of the Database class
For instance :
conn.keys() returns the list of the database names
conn[db_name] returns the instance of the Database class called db_name
del conn[db_name] drops the database called db_name (all the tables in this database must have been deleted first)
They support another method :
create(db_name) : creates a database named db_name and returns the instance of the Database class
Database
Instances of Database are dictionary-like objects : the keys are the table names and the values are the matching instances of the Table class. For instance :
db.keys() returns the list of the table names in the database
db[table_name] returns the instance of the Table class called table_name
del db[table_name] : drops the table called table_name
They support another method :
create(table_name,*fields[,**mode]) : creates a table named table_name and returns the instance of the Table class
fields are 2-element tuples (field_name,field_type) where field_name is the field name and field_type is a MySQL field type : INTEGER, REAL, DATETIME, CHAR, BLOB, etc. :
db.create('foo',('name','CHAR(20)'),('age','INTEGER'),('size','REAL'))
If additional information on the type needs to be provided, put it in the second argument, using the SQL syntax for MySQL :
db.create('foo',('recid','INTEGER PRIMARY KEY AUTO_INCREMENT'),
('date','TIMESTAMP DEFAULT CURRENT_TIMESTAMP'))
The optional keyword argument mode specifies what to do if a table names table_name already exists :
mode="open" : returns the instance of Table for this table, ignores field definitions
mode="override" : drops the table and creates a new one with the field definitions
- if mode is not specified, an exception is raised
Table
For record insertion, selection, update and deletion, adding or dropping fields, the syntax is the same as with the pure-Python module
Direct access by record identifier is only possible si a field has been defined as a primary key or has the mode AUTO_INCREMENT ; functions delete() and update() alos require a such field
To insert many records at a time,
table.insert(list_of_values)
will be much faster than
for values in list_of_values:
table.insert(values)
Selection by record id only works if a field INTEGER PRIMARY KEY is present in the table ; the functions delete() and update() also require this record id
cursor and commit
Intances of Database and Table all have :
|