SQL (SQLAlchemy)

Extension module providing some convenience functions for working with SQL databases. SQLAlchemy is required, try apt-get install python-sqlalchemy or pip install SQLAlchemy.

Acknowledgments: much of the code of this module is based on the csvsql utility in the csvkit package.

petlx.sql.todb(table, dbo, tablename, schema=None, commit=True, create=False, drop=False, constraints=True, metadata=None, dialect=None, sample=1000)[source]

Drop-in replacement for petl.todb() which also supports automatic table creation.

table : sequence of sequences (petl table)
Table data to load
dbo : database object
DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or SQLAlchemy connection, engine or session
tablename : string
Name of the table
schema : string
Name of the database schema to create the table in
commit : bool
If True commit the changes
create : bool
If True attempt to create the table before loading, inferring types from a sample of the data
drop : bool
If True attempt to drop the table before recreating (only relevant if create=True)
constraints : bool
If True use length and nullable constraints (only relevant if create=True)
metadata : sqlalchemy.MetaData
Custom table metadata (only relevant if create=True)
dialect : string
One of {‘access’, ‘sybase’, ‘sqlite’, ‘informix’, ‘firebird’, ‘mysql’, ‘oracle’, ‘maxdb’, ‘postgresql’, ‘mssql’} (only relevant if create=True)
sample : int
Number of rows to sample when inferring types etc. Set to 0 to use the whole table.
petlx.sql.create_table(table, dbo, tablename, schema=None, commit=True, constraints=True, metadata=None, dialect=None, sample=1000)[source]

Create a database table based on a sample of data in the given table.

table : sequence of sequences (petl table)
Table data to load
dbo : database object
DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or SQLAlchemy connection, engine or session
tablename : string
Name of the table
schema : string
Name of the database schema to create the table in
commit : bool
If True commit the changes
constraints : bool
If True use length and nullable constraints (only relevant if create=True)
metadata : sqlalchemy.MetaData
Custom table metadata (only relevant if create=True)
dialect : string
One of {‘access’, ‘sybase’, ‘sqlite’, ‘informix’, ‘firebird’, ‘mysql’, ‘oracle’, ‘maxdb’, ‘postgresql’, ‘mssql’} (only relevant if create=True)
sample : int
Number of rows to sample when inferring types etc., set to 0 to use the whole table (only relevant if create=True)
petlx.sql.drop_table(dbo, tablename, schema=None, commit=True)[source]

Drop a database table if it exists.

dbo : database object
DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or SQLAlchemy connection, engine or session
tablename : string
Name of the table
schema : string
Name of the database schema the table is in
commit : bool
If True commit the changes
petlx.sql.make_create_table_statement(table, tablename, schema=None, constraints=True, metadata=None, dialect=None)[source]

Generate a CREATE TABLE statement based on a petl table.

table : sequence of sequences (petl table)
Table data to use to infer types etc.
tablename : string
Name of the table
schema : string
Name of the database schema to create the table in
constraints : bool
If True use length and nullable constraints
metadata : sqlalchemy.MetaData
Custom table metadata
dialect : string
One of {‘access’, ‘sybase’, ‘sqlite’, ‘informix’, ‘firebird’, ‘mysql’, ‘oracle’, ‘maxdb’, ‘postgresql’, ‘mssql’}
petlx.sql.make_sqlalchemy_table(table, tablename, schema=None, constraints=True, metadata=None)[source]

Create an SQLAlchemy table based on a petl table.

table : sequence of sequences (petl table)
Table data to use to infer types etc.
tablename : string
Name of the table
schema : string
Name of the database schema to create the table in
constraints : bool
If True use length and nullable constraints
metadata : sqlalchemy.MetaData
Custom table metadata
petlx.sql.make_sqlalchemy_column(col, colname, constraints=True)[source]

Infer an appropriate SQLAlchemy column type based on a sequence of values.

col : sequence
A sequence of values to use to infer type, length etc.
colname : string
Name of column
constraints : bool
If True use length and nullable constraints