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