Français

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 :
  • the attribute cursor, the MySQL connections cursor, so you can also execute SQL expressions by
    db.cursor.execute(some_sql)
    and get the result by
    results = db.cursor.fetchall()
  • the method commit() to commit changes to a database after a transaction