ncl-sqlsnippets 1.2.0

Module containing standard python functions to interact with the sandpit

NCL SQLSnippets

This is a python module adding a standard set of functions to interact with the sandpit

• NCL SQLSnippets
  • History
    • [1.0] - 10/07/2023
    • [1.1] - 10/08/2023
  • Installation
  • Standard Use
  • Functions
    • get_connection_string
    • connect_to_sql
    • connect
    • upload_to_sql
    • list_all_tables
    • table_exists
    • backup_table
    • restore_table
    • list_all_columns
    • columns_exist
    • execute_query
    • execute_sfw
  • License

History

[1.0] - 10/07/2023

• Initial set of functions
• Instructions for installing as a module

[1.1] - 10/08/2023

• Added connect function

Installation

• Clone the repo to your local python site-packages directory. It is recommended to create a dedicated ncl folder to organise user created modules and seperate them from other modules installed through pip. The init.py and sqlsnippets.py files should be in the root of the sqlsnippets directory on your machine.

C:\Users\{USERNAME}\AppData\Local\Programs\Python\Python311\Lib\site-packages\ncl\sqlsnippets

• Install pandas and SQLAlchemy. Can be done with the requirements file:

pip install -r requirements.txt

Standard Use

• In your other python and notebook files you can add the sqlsnippets module like any other standard module.

import ncl.sqlsnippets as snips

• Likewise functions from sqlsnippets can be called like functions from any standard module

#Build connection string
conn_str = snips.get_connection_string(SQL_ADDRESS, SQL_DATABASE)

#Connect to database
engine = snips.connect_to_sql(conn_str=conn_str)

#Upload data
snips.upload_to_sql(data, engine, SQL_TABLE, SQL_SCHEMA, replace=False)

Functions

get_connection_string

get_connection_string (server_addres, database, server_type="mssql", driver="SQL+Server")

Returns a connection string for the sandpit.

Parameters:

• server_address: str
  The server name of the database. It can be found in the Connect to Server pop-up when openning MS SQL Server Management Studio.

• database: str
  Name of the database.

• server_type: str
  Type of SQL server. Does not need to be set unless we ever migrate from MS SQL.

• driver: str
  Driver for the SQL server. Does not need to be set unless we ever migrate from MS SQL.

Returns: str
Connection string to establish the connection in the connect_to_sql function.

connect_to_sql

connect_to_sql (conn_str)

Takes a connection string and returns a connection to the database in the form of an engine.

Parameters:

• conn_str: str
  Connection string.

Returns: engine
Connection to the database. Store this in a variable and use it as a parameter in the other sqlsnippets functions.

connect

connect (server_address, database)

Wrapper for the main connection functions. Returns the engine connection object.

Parameters:

• server_address: str
  The server name of the database. It can be found in the Connect to Server pop-up when openning MS SQL Server Management Studio.

• database: str
  Name of the database.

upload_to_sql

upload_to_sql (data, engine, table, schema, replace, chunks=100, dtypes={})

Takes data and uploads it to a target table in the database. If the table does not exist it will be created.

Parameters:

• data: DataFrame
  Data to upload in the form of a pandas data frame.

• engine: engine
  Engine object from the connect_to_sql function.

table: str
Name of the table in the database.

schema: str
Name of the schema in the database (this should either be "dbo" or your BLUE account prefix in most cases).

replace: Boolean
If set to True, this function will replace any existing data in the table. If set to False it will append the data instead.
Warning: This will drop the original table when set to True

chunks: int
This specifies the block size for data being uploaded. Generally bigger blocks means faster uploads. The function will fail if the block size exceeds the maximum limit for MS SQL (2100). As a rule of thumb, this figure should not exceed 2100/(number of columns).

dtypes: dict
Optional parameter to specify the types of the data columns, useful for specifying when a column should be intrepretted by the server as a date value. You can choose to only specify the type for a subset of the columns.

dtypes = {
    "date" : types.Date
}

A list of dtype values recognised by the function are listed here: https://docs.sqlalchemy.org/en/20/core/type_basics.html

Returns: None

list_all_tables

list_all_tables (engine)

Lists all tables in a database. This will use the database specified in the connection string used.

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

Returns: DataFrame
Returns a database with the following columns: SchemaName, TableName.

table_exists

table_exists (engine, table, schema)

Checks if a given table exists in the database. The schema for the table needs to be provided as well.

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

table: str
Name of the table in the database.

schema: str
Name of the schema in the database (this should either be "dbo" or your BLUE account prefix in most cases).

Returns: Boolean
Returns True if [schema].[table] exists in the database.

backup_table

backup_table (engine, table, schema)

Creates a backup of the specified table ([database].[schema].[table]). The backup table will have the same name as the original but with "_backup" as a suffix.

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

table: str
Name of the table in the database.

schema: str
Name of the schema in the database (this should either be "dbo" or your BLUE account prefix in most cases).

Returns: None \

restore_table

restore_table (engine, table, schema)

Replaces the table specified ([database].[schema].[table]) and replaces it with the backup table. Any table with the same name as the specified table but with "_backup" as a suffix to the table name will be considered to be the backup table.

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

table: str
Name of the table in the database.

schema: str
Name of the schema in the database (this should either be "dbo" or your BLUE account prefix in most cases).

Returns: None \

list_all_columns

list_all_columns (engine, table, schema)

Lists all columns for a given table. This will use the database specified in the connection string used.

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

table: str
Name of the table in the database.

schema: str
Name of the schema in the database (this should either be "dbo" or your BLUE account prefix in most cases).

Returns: Series
Returns a list of columns in the table in the form of a pandas Series.

columns_exist

columns_exist (engine, table, schema, columns)

Checks if given columns exists in the specified table. The schema for the table needs to be provided as well.

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

table: str
Name of the table in the database.

schema: str
Name of the schema in the database (this should either be "dbo" or your BLUE account prefix in most cases).

Returns: Array
Returns a list of all missing columns.
Warning: If all columns exist in the table then this returns an empty list which can be logically treated as False.

execute_query

execute_query (engine, query)

Executes a generic query. For queries where you expect rows to be returned use execute_sfw(). Warning: This function commits changes to the database at the end of every call.

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

query: str
The query to execute as a string

Returns: None \

execute_sfw

execute_sfw (engine, query)

Executes a Select From Where query. The function is for queries where you expect a response to process, for other queries use execute_query().

Parameters:

• engine: engine
  Engine object from the connect_to_sql function.

query: str
The query to execute as a string

Returns: DataFrame
The query result is returned as a pandas data frame.

License

This repository is dual licensed under the Open Government v3 & MIT. All code can outputs are subject to Crown Copyright.