API

class sqlsorcery.BigQuery(dataset=None, creds=None, project_id=None)

Bases: sqlsorcery.Connection

Child class that inherits from Connection with specific configuartion for connecting to Google BigQuery.

Initializes a BigQuery database connection

Note

When objects is instantiated without params, SQLSorcery will attempt to pull the values from the environment. See the README for examples of setting these correctly in a .env file.

Parameters:
  • dataset (string) – Google dataset name
  • creds (string) – Filepath to service account credentials json file
  • project_id (string) – Google BigQuery project id
insert_into(table, df, if_exists='append', chunksize=None)

Inserts the data in a pandas dataframe into a specified sql table

Parameters:
  • table (string) – Name of bigquery table in the form of dataset.tablename
  • df (Pandas.DataFrame) – DataFrame to be inserted
  • if_exists (string) – How to behave if the table already exists. Possible options: fail, append, replace. Default = append
  • chunksize (int) – Size of batch for inserts (default is all at once)
Returns:

None
query(sql_query)

Executes the given sql query

Parameters:sql_query (string) – SQL query string
Returns:Resulting dataset from query
Return type:Pandas.DataFrame
query_from_file(filename)

Executes the given query from a provided sql file

Parameters:filename (string) – Path to .sql file containing a query
Returns:Resulting dataset from query
Return type:Pandas.DataFrame
class sqlsorcery.Connection

Bases: object

Base class for sql connections containing shared class methods.

Note

This parent class is not meant to be called publicly and should only be used for inheritance in the specific connection types.

delete(tablename)

Deletes all records in a given table. Does not reset identity columns.

Parameters:tablename (string) – Name of the table to delete records for
exec_cmd(command)

Executes an arbitrary sql command on the database.

Note

Security Warning: This command is vulnerable to SQL-injection. Do not use in conjunction with arbitrary user input.

Parameters:command (string) – The SQL command to be executed
exec_cmd_from_file(filename)

Executes an arbitrary sql command provided from a .sql file.

Parameters:filename (string) – Path to .sql file containing a query
Returns:Resulting dataset from query
Return type:Pandas.DataFrame
exec_sproc(stored_procedure, autocommit=False)

Executes a stored procedure

Note

Security Warning: This command leverages interpolated strings and as such is vulnerable to SQL-injection. Do not use in conjunction with arbitrary user input.

Parameters:
  • stored_procedure (string) – The name of the stored procedure to be executed.
  • autocommit (boolean) – Determines how to handle transactions (default=False)
Returns:

Stored procedure results
Return type:

SQLAlchemy.ResultProxy
get_columns(table)

Returns the column definitions for a given table.

Parameters:table (string) – The name of the table to inspect. Do not include the schema prefix.
Returns:A list of column definition dictionaries
Return type:list
get_view_definition(view)

Returns the view definition (DDL) for a given SQL view.

Parameters:view (string) – The name fo the view to inspect. Do not include the schema prefix.
Returns:Multi-line string of the view definition text
Return type:string
insert_into(table, df, if_exists='append', chunksize=None, dtype=None)

Inserts the data in a pandas dataframe into a specified sql table

Parameters:
  • table (string) – Name of sql table to insert data into
  • df – DataFrame to be inserted
  • if_exists (string) – How to behave if the table already exists. Possible options: fail, append, replace. Default = append
  • chunksize (int) – Size of batch for inserts (default is all at once)
  • dtype – Explicitly specify the data type for columns
Returns:

None
query(sql_query, params=None)

Executes the given sql query

Parameters:
  • sql_query (string) – SQL query string
  • params (list or dict) – list or dict of parameters to pass to sql query

Note

See PEP249 for possible paramstyles.

Returns:Resulting dataset from query
Return type:Pandas.DataFrame
query_from_file(filename)

Executes the given sql query from a provided sql file

Parameters:filename (string) – Path to .sql file containing a query
Returns:Resulting dataset from query
Return type:Pandas.DataFrame
table(tablename)

Returns a SQLAlchemy table object for further manipulation such as updates.

Parameters:tablename (string) – Name of the table to return
Returns:A table
Return type:SQLAlchemy.Table
truncate(tablename)

Truncates a given table. Faster than a delete and reseeds identity values.

Note

Security Warning: This command leverages interpolated strings and as such is vulnerable to SQL-injection. Do not use in conjunction with arbitrary user input. Instead, use .delete()

Parameters:tablename (string) – Name of the table to truncate
class sqlsorcery.MSSQL(schema=None, port=None, server=None, db=None, user=None, pwd=None)

Bases: sqlsorcery.Connection

Child class that inherits from Connection with specific configuration for connecting to MS SQL.

Initializes an MS SQL database connection

Note

When object is instantiated without params, SQLSorcery will attempt to pull the values from the environment. See the README for examples of setting these correctly in a .env file.

Parameters:
  • schema (string) – Database object schema prefix
  • server (string) – IP or URL of database server
  • db (string) – Name of database
  • user (string) – Username for connecting to the database
  • pwd (string) – Password for connecting to the database. Security Warning: always pass this in with environment variables when used in production.
class sqlsorcery.MySQL(server=None, port=None, db=None, user=None, pwd=None)

Bases: sqlsorcery.Connection

Child class that inherits from Connection with specific configuration for connecting to a MySQL database.

Initializes a MySQL database connection

Note

When object is instantiated without params, SQLSorcery will attempt to pull the values from the environment. See the README for examples of setting these correctly in a .env file.

Parameters:
  • server (string) – IP or URL of database server
  • port (string) – Port number
  • db (string) – Name of database
  • user (string) – Username for connecting to the database
  • pwd (string) – Password for connecting to the database. Security Warning: always pass this in with environment variables when used in production.
class sqlsorcery.Oracle(schema=None, server=None, port=None, sid=None, user=None, pwd=None)

Bases: sqlsorcery.Connection

Child class that inherits from Connection with specific configuration for connecting to Oracle PL/SQL.

Initializes an Oracle database connection

Note

When object is instantiated without params, SQLSorcery will attempt to pull the values from the environment. See the README for examples of setting these correctly in a .env file.

Parameters:
  • schema (string) – Database object schema prefix
  • server (string) – IP or URL of database server
  • port (string) – Port number
  • sid (string) – Database site identifier
  • user (string) – Username for connecting to the database
  • pwd (string) – Password for connecting to the database. Security Warning: always pass this in with environment variables when used in production.
class sqlsorcery.PostgreSQL(schema=None, server=None, port=None, db=None, user=None, pwd=None)

Bases: sqlsorcery.Connection

Child class that inherits from Connection with specific configuration for connecting to PostgreSQL.

Initializes a PostgreSQL database connection

Note

When object is instantiated without params, SQLSorcery will attempt to pull the values from the environment. See the README for examples of setting these correctly in a .env file.

Parameters:
  • schema (string) – Database object schema prefix
  • server (string) – IP or URL of database server
  • port (string) – Port number
  • db (string) – Name of database
  • user (string) – Username for connecting to the database
  • pwd (string) – Password for connecting to the database. Security Warning: always pass this in with environment variables when used in production.
exec_sproc(stored_procedure, autocommit=False)

Executes a stored procedure using the Postgres syntax

Note

Security Warning: This command leverages interpolated strings and as such is vulnerable to SQL-injection. Do not use in conjunction with arbitrary user input.

Parameters:
  • stored_procedure (string) – The name of the stored procedure to be executed.
  • autocommit (boolean) – Determines how to handle transactions (default=False)
Returns:

Stored procedure results
Return type:

SQLAlchemy.ResultProxy
class sqlsorcery.SQLite(path=None)

Bases: sqlsorcery.Connection

Child class that inherits from Connection with specific configuration for connecting to a SQLite database file.

Initializes a SQLite database connection

Parameters:path – Path to the .db file