SQL databases

Note

Structured Query Language (SQL) databases are wildly used in web infrastructure, and are also used to store large datasets in Science. Several flavors exist, the most popular of which are SQLite, MySQL, and PostGreSQL.

SQL databases are supported in ATpy thanks to the sqlite module built-in to Python, the MySQL-python module, and the PyGreSQL module. When reading from databases, the first argument in Table should be the database type (one of sqlite, mysql, and postgres). For SQLite databases, which are stored in a file, reading in a table is easy:

>>> t = atpy.Table('sqlite', 'mydatabase.db')

If more than one table is present in the file, the table name can be specified:

>>> t = atpy.Table('sqlite', 'mydatabase.db', table='observations')

For MySQL databases, standard MySQL parameters can be specified. These include user, passwd, db (the database name), host, and port. For PostGreSQL databases, standard PostGreSQL parameters can be specified. These include user, password, database, and host.

For example, to read a table called velocities from a MySQL database called measurements, with a user monty and password spam, one would use:

>>> t = atpy.Table('mysql', user='monty', passwd='spam',
                   db='measurements', table='velocities')

To read in all the tables in a database, simply use the TableSet class, e.g:

>>> t = atpy.TableSet('sqlite', 'mydatabase.db')

or

>>> t = atpy.TableSet('mysql', user='monty', passwd='spam',
                      db='measurements')

It is possible to retrieve only a subset of a table, or the result of any standard SQL query, using the query argument. For example, the following will retrieve all entries where the quality variable is positive:

>>> t = atpy.Table('mysql', user='monty', passwd='spam',
                   db='measurements', table='velocities',
                   query='SELECT * FROM velocities WHERE quality > 0;' )

Any valid SQL command should work, including commands used to merge different tables.

Writing tables or table sets to databases is simple, and is done through the write method. As before, database parameters may need to be specified, e.g.:

>>> t.write('sqlite', 'mydatabase.db')

or

>>> t.write('mysql', user='monty', passwd='spam',
            db='measurements')

Note

As for file formats, the verbose argument can be specified to control whether warning messages are shown when reading (the default is verbose=True), and the overwrite argument can be used when writing to overwrite a file (the default is overwrite=False).

Full API for advanced users

Note

The following functions should not be called directly - the arguments should be passed to Table()/Table.read(), Table.write(), TableSet()/TableSet.read(), and TableSet.write() respectively.

atpy.sqltable.read(self, dbtype, *args, **kwargs)

Required Arguments:

dbtype: [ ‘sqlite’ | ‘mysql’ | ‘postgres’ ]

The SQL database type

Optional arguments (only for Table.read() class):

table: [ string ]

The name of the table to read from the database (this is only required if there are more than one table in the database). This is not required if the query= argument is specified, except if using an SQLite database.

query: [ string ]

An arbitrary SQL query to construct a table from. This can be any valid SQL command provided that the result is a single table.

The remaining arguments depend on the database type:

• SQLite:

  Arguments are passed to sqlite3.connect(). For a full list of available arguments, see the help page for sqlite3.connect(). The main arguments are listed below.

  Required arguments:

  dbname: [ string ]

  The name of the database file

• MySQL:

  Arguments are passed to MySQLdb.connect(). For a full list of available arguments, see the documentation for MySQLdb. The main arguments are listed below.

  Optional arguments:

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  passwd: [ string ]

  The user password (default is blank)

  db: [ string ]

  The name of the database to connect to (no default)

  port [ integer ]

  The port to connect to (default is 3306)

• PostGreSQL:

  Arguments are passed to pgdb.connect(). For a full list of available arguments, see the help page for pgdb.connect(). The main arguments are listed below.

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  password: [ string ]

  The user password (default is blank)

  database: [ string ]

  The name of the database to connect to (no default)

atpy.sqltable.write(self, dbtype, *args, **kwargs)

Required Arguments:

dbtype: [ ‘sqlite’ | ‘mysql’ | ‘postgres’ ]

The SQL database type

Optional arguments (only for Table.read() class):

table: [ string ]

The name of the table to read from the database (this is only required if there are more than one table in the database). This is not required if the query= argument is specified, except if using an SQLite database.

query: [ string ]

An arbitrary SQL query to construct a table from. This can be any valid SQL command provided that the result is a single table.

The remaining arguments depend on the database type:

• SQLite:

  Arguments are passed to sqlite3.connect(). For a full list of available arguments, see the help page for sqlite3.connect(). The main arguments are listed below.

  Required arguments:

  dbname: [ string ]

  The name of the database file

• MySQL:

  Arguments are passed to MySQLdb.connect(). For a full list of available arguments, see the documentation for MySQLdb. The main arguments are listed below.

  Optional arguments:

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  passwd: [ string ]

  The user password (default is blank)

  db: [ string ]

  The name of the database to connect to (no default)

  port [ integer ]

  The port to connect to (default is 3306)

• PostGreSQL:

  Arguments are passed to pgdb.connect(). For a full list of available arguments, see the help page for pgdb.connect(). The main arguments are listed below.

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  password: [ string ]

  The user password (default is blank)

  database: [ string ]

  The name of the database to connect to (no default)

atpy.sqltable.read_set(self, dbtype, *args, **kwargs)

Required Arguments:

dbtype: [ ‘sqlite’ | ‘mysql’ | ‘postgres’ ]

The SQL database type

Optional arguments (only for Table.read() class):

table: [ string ]

The name of the table to read from the database (this is only required if there are more than one table in the database). This is not required if the query= argument is specified, except if using an SQLite database.

query: [ string ]

An arbitrary SQL query to construct a table from. This can be any valid SQL command provided that the result is a single table.

The remaining arguments depend on the database type:

• SQLite:

  Arguments are passed to sqlite3.connect(). For a full list of available arguments, see the help page for sqlite3.connect(). The main arguments are listed below.

  Required arguments:

  dbname: [ string ]

  The name of the database file

• MySQL:

  Arguments are passed to MySQLdb.connect(). For a full list of available arguments, see the documentation for MySQLdb. The main arguments are listed below.

  Optional arguments:

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  passwd: [ string ]

  The user password (default is blank)

  db: [ string ]

  The name of the database to connect to (no default)

  port [ integer ]

  The port to connect to (default is 3306)

• PostGreSQL:

  Arguments are passed to pgdb.connect(). For a full list of available arguments, see the help page for pgdb.connect(). The main arguments are listed below.

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  password: [ string ]

  The user password (default is blank)

  database: [ string ]

  The name of the database to connect to (no default)

atpy.sqltable.write_set(self, dbtype, *args, **kwargs)

Required Arguments:

dbtype: [ ‘sqlite’ | ‘mysql’ | ‘postgres’ ]

The SQL database type

Optional arguments (only for Table.read() class):

table: [ string ]

The name of the table to read from the database (this is only required if there are more than one table in the database). This is not required if the query= argument is specified, except if using an SQLite database.

query: [ string ]

An arbitrary SQL query to construct a table from. This can be any valid SQL command provided that the result is a single table.

The remaining arguments depend on the database type:

• SQLite:

  Arguments are passed to sqlite3.connect(). For a full list of available arguments, see the help page for sqlite3.connect(). The main arguments are listed below.

  Required arguments:

  dbname: [ string ]

  The name of the database file

• MySQL:

  Arguments are passed to MySQLdb.connect(). For a full list of available arguments, see the documentation for MySQLdb. The main arguments are listed below.

  Optional arguments:

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  passwd: [ string ]

  The user password (default is blank)

  db: [ string ]

  The name of the database to connect to (no default)

  port [ integer ]

  The port to connect to (default is 3306)

• PostGreSQL:

  Arguments are passed to pgdb.connect(). For a full list of available arguments, see the help page for pgdb.connect(). The main arguments are listed below.

  host: [ string ]

  The host to connect to (default is localhost)

  user: [ string ]

  The user to conenct as (default is current user)

  password: [ string ]

  The user password (default is blank)

  database: [ string ]

  The name of the database to connect to (no default)