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.

Parameters:

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.

Parameters:

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.

Parameters:

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.

Parameters:

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.

Parameters:

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.

Parameters:

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