__getattr__(self, key)
(Qualification operator)

Overrides:

picalo.base.Global.AbstractDecorator.__getattr__

__repr__(self)
(Representation operator)

Overrides:

picalo.base.Global.AbstractDecorator.__repr__

__str__(self)
(Informal representation operator)

Overrides:

picalo.base.Global.AbstractDecorator.__str__

commit(self)

Commits any changes to the database. If you make modifications of any kind to your database tables (INSERT, UPDATE, CREATE, etc.), you *must* commit those changes by calling commit().

If you close a database connection without committing, you'll lose all changes.

Analyses that only run SELECT calls on a database do not need to commit().

copy_table(self, source_connection, source_tablename, dest_tablename, replace=False)

Copies a table from a database into this connection. This method pulls records one by one from the source connection and posts them to this connection. It automatically creates the new destination table, optionally replacing any existing tables with the name.

This is a convenience method provided to make importing of data from other databases easy. This method is different from post_table because it is more memory-efficient. The post_table method requires that you have an existing Picalo Table, which means all of the data needs to be in memory at once. This method copies record by record, meaning it only needs to hold one record at a time. This method works only with database connections, not with CSV or other types of sources.

The method always tries to create a new table in the database -- it will not append records into an existing table. If the table cannot be created (if it exists, for example), an error is thrown. If the "replace" option is True, any existing tables by this name are dropped before the new table is created.

cursor(self)

Returns:

A cursor to the database.
           (type=_Cursor)

execute(self, sql, parameters=None, close_cursor=True)

Runs executable queries (INSERT, UPDATE, DELETE) that don't return tables. This is the primary methods to use when posting information to a database.

IMPORTANT: Since most databases don't commit changes automatically, you *must* call commit() to make the changes permanent. If you close the connection without committing, you'll lose all of your changes.

Alternatively, you can call rollback() to undo all changes since the last commit() call.

>>> myconn.execute("CREATE TABLE mytable (id integer, name varchar(20))")
>>> myconn.execute("INSERT INTO mytable (id, name) VALUES (1, 'Dennis')")
>>> myconn.commit()

Parameters:

sql - The SQL string to execute.
           (type=string)

parameters - The parameters to be sent to the database.
           (type=List or Tuple)

close_cursor - Determines whether the cursor is closed automatically. Unless you need the cursor for something else, you should allow it to close automatically.
           (type=bool)

Returns:

The return value from the database. This varies from database to database and is probably not very useful. If errors occur in the operation, exceptions are thrown.
           (type=object)

insert_query_builder(self, tablename)

This method returns a helper class for creating INSERT queries. It allows you to create INSERT queries piece by piece and is available for advanced users. It is useful when creating an SQL query programatically in a script.

Parameters:

tablename - The database table name to insert the records into
           (type=str)

Returns:

An InsertQueryBuilder object.
           (type=InsertQueryBuilder)

is_changed(self)

list_tables(self, refresh=True)

post_table(self, table, name, replace=False, add_if_empty=False)

Creates a new table in the database and posts the records in the given table. If a table with the given name already exists in the table, the method throws an error unless the "replace" option is True.

This is a convenience method provided to make importing of data from text files (CSV, TSV, etc.) and other sources into databases easy. The example shows how a CSV file can be posted to database in just two lines of code.

The method always tries to create a new table in the database -- it will not append records into an existing table. If the table cannot be created (if it exists, for example), an error is thrown. If the "replace" option is True, any existing tables by this name are dropped before the new table is created.

The method automatically commits the data to the database. You cannot use rollback after this method has finished. This is required by some databases after creation of a table, so it has to be done.

>>> myconn = Database.PostgreSQLConnection('mydb')
>>> data = Table([('id', int), ('name', unicode)],[
...   [ 1, 'Benny' ],
...   [ 2, 'Vijay' ],
... ])
>>> myconn.post_table(data, 'mytable', True)
>>> myconn.table('SELECT * FROM mytable').view()
+----+-------+
| id |  name |
+----+-------+
|  1 | Benny |
|  2 | Vijay |
+----+-------+

Parameters:

table - The picalo table to post
           (type=Table)

name - The new database table name
           (type=str)

replace - Whether to replace any existing database tables with the given name. If the database table exists and replace is False (the default), the method will throw an error.
           (type=bool)

add_if_empty - Whether to add fields that are empty (None or '') or to simply leave them out of the query.
           (type=bool)

query(self, sql, parameters=None)

Runs a SELECT query that returns a result set. This method provides efficient access to extremely large data sets, but it is not as easy to use as table(). Most users should use table() unless you explicitly need to access only one record at a time. In other words, check out Database.Connection.table(sql) and use it unless you really need the efficiency offered by this method.

Stated differently, query() and table() accomplish the same task: query a database. The query() method is more efficient and the table() method is more powerful and easier to use.

If you need to efficiently use memory and must use this method, you should use an iterator in a for loop as is done in the example. This allows you to access one record at a time. Note that you can only go in forward direction one at a time. If you need to go backwards in your analysis, either store the records in variables or use table().

>>> import psycopg
>>> myconn = Database.Connection(psycopg.connect(database='mydb'))
>>> for record in myconn.query("SELECT * FROM mytable"):
...   print record['id']
...   print record['name']
...

1
Dennis

Parameters:

sql - The SQL string to execute.
           (type=string)

parameters - The parameters to be sent to the database.
           (type=List or Tuple)

Returns:

A cursor to the result set.
           (type=Cursor)

query1(self, sql, parameters=None)

Runs a SELECT query and returns only the first record from the result set. There are many times when you expect only a single record in the result set, such a key value query. In these circumstances, query1() provides easy access directly to the first record.

The method returns a single Record instance or None if no results are found.

>>> import psycopg
>>> myconn = Database.Connection(psycopg.connect(database='mydb'))
>>> rec = myconn.query1("SELECT min(id) FROM mytable")
>>> print rec
1

Parameters:

sql - The SQL string to execute.
           (type=string)

parameters - The parameters to be sent to the database.
           (type=List or Tuple)

Returns:

A single result set record.
           (type=Record)

refresh(self, tablename=None)

Parameters:

tablename - The relation name to refresh, None to refresh all tables in this database.
           (type=str)

save(self, filename)

select_query_builder(self, tablename, select_fields=['*'])

This method returns a helper class for creating simple SELECT queries. It allows you to create SELECT queries piece by piece and is available for advanced users. It is useful when creating an SQL query programatically in a script.

Parameters:

tablename - The database table name to update the records in.
           (type=str)

select_fields - The fields to be selected by the query.
           (type=list or tuple of strings)

Returns:

An SelectQueryBuilder object.
           (type=SelectQueryBuilder)

table(self, sql, parameters=None)

This method is the primary access to databases within Picalo. Most users will create a connection and then call only this method to retrieve data from tables.

Retrieves a Table to the results of a new query or the most recently-run query. Table objects have additional methods not found in this module, such as pretty printing, saving to text files, and mutability.

If your database driver is one of the enhanced support drivers (such as PythonWin's odbc, psycopg, and MySQLdb drivers), the method will automatically set column types.

>>> import psycopg
>>> myconn = Database.Connection(psycopg.connect(database='conan'))
>>> data = myconn.table("SELECT * FROM mytable")
>>> data.view()
+----+--------+
| id |  name  |
+----+--------+
|  1 | Dennis |
+----+--------+

Parameters:

sql - The SQL string to execute.
           (type=string)

parameters - The parameters to be sent to the database.
           (type=List or Tuple)

Returns:

A picalo table containing the results.
           (type=Table)

update_query_builder(self, tablename)

This method returns a helper class for creating simple UPDATE queries. It allows you to create UPDATE queries piece by piece and is available for advanced users. It is useful when creating an SQL query programatically in a script.

Parameters:

tablename - The database table name to update the records in.
           (type=str)

Returns:

An UpdateQueryBuilder object.
           (type=UpdateQueryBuilder)

_create_database_table(self, table, name, replace=False)

_get_columns(self, tablename)

_set_table_types(self, cursor, table)