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