p2sql 0.0.1

Description:

p2sql 0.0.1

p2sql
p2sql (Pandas and PYODBC To SQL) provides a simpler way to manage communications between python and SQL using dependencies such as:

pandas
pyodc
and numpy

Getting Started
import p2sql

Functions
connection_string()
connection_string(
Server: str,
Database = 'master',
Driver = '{SQL Server}',
IntegratedSecurity = True,
user=None,
passw=None
)

conection_string() stores the conection information, the only parameters that are required are the Server, ODBC Driver
Note: It is recommended to supply the databasename, by default "master" will be used
conn_str_master = p2sql.connection_string('servername')

The value of the variable conn_str_master will be:
Driver={SQL Server};
Server=servername;
Database=master;
Trusted_Connection=yes;

You can create a different p2sql connection string by specifying the database or by setting intergrated security to false and setting the username and password parameters.
Example:
conn_str_master = p2sql.connection_string (
'Server Name',
'{Enter Driver name}',
'Database Name',
False,
'UserName',
'Password'
)

connection()
connection( conection_string() )

Use connection() to establish a connectin to server. Here we use the above created connection string as the connection string parameter.
Note: You could write a custom connection string manually in the p2sql.connection() function


Example:
p2sql.connection('Driver={SQL Server}; Server=servername; Database=master; Trusted_Connection=true;')

or
connection = p2sql.connection(conn_str_master)

executeCreateDB()
executeCreateDB( connection(), listOfDBNames, printQuery=False )

Use executeCreateDB() to create database(s) by using the executeCreateDB function , passing the conneciton variable and a python list of databasename strings
p2sql.executeCreateDB(connection(),['TestDatabase','databaseTest2'],True)

getListOfDbs( )
getListOfDbs( connection() )

Use getListOfDbs() to get the list of database, by passing the connection function to getListOfDBs
listOfDbs = p2sql.getListOfDbs(connection)

getListOfDbs() will result to a list of tuples containing (databaseName, databaseID)
Example:
[('master', 1), ('tempdb', 2), ('model', 3), ('msdb', 4), ('TestDatabase', 5), ('databaseTest2', 6)]

createTblQuery()
createTblQuery( database:str, tablename:str, columns:list, dataTypes=False, schema='dbo' )

createTblQuery() requires a database name, a table name, and a list of lists,
Example: [['Column Name','Data Type']]
where index 0 is the name of the column and index 1 is the SQL data type
columnsWithDataTypes = [
['name', 'nvarchar(255) NOT NULL' ],
['age', 'int NULL' ],
['location', 'nvarchar(100) NULL']
]

A list of columns without data types will default create a table with data types of 'nvarchar(MAX) NULL'
Example:
columnsWithoutDataTypes = [
['name'],
['age'],
['location']
]

TestTableQuery = p2sql.createTblQuery('databaseTest2','TestTable',columnsWithDataTypes,True)

Contents Of TestTableQuery variable:
IF OBJECT_ID('TestTable') IS Null
CREATE TABLE [TestDatabase].[dbo].[TestTable](
name nvarchar(255) NOT NULL,
age int NULL ,
location NVARCHAR(100) NULL
)

executeQueries()
executeQueries( connection(), listOfQueries, printQuery=False )

executeQueries() executes a list of query strings such as insert,create,update,etc. This function does not return a value it loops through a list of supplied queries and executes the query using the module pyodbc connection
p2sql.executeQueries(connection,[TestTableQuery])

Any executable query can replace the variable [TestTableQuery]
Example:
insertQuery = ["""
INSERT INTO [databaseTest2].[dbo].[TestTable2]
(
name,
age,
location
)
VALUES
(
'Daivd',
'45',
'Detroit'
);
"""]

p2sql.executeQueries(connection,[insertQuery])

this will execute the custom created insert query
Another way to insert values into a table without writing the query is to use the putInTable() function
putInTable()
putInTable(
connection(),
tablename: str,
listOfColumnNames: list,
listOfListValues: list ,
Database="" ,
schema='dbo',
printQuery = False
)

putInTable() will create and run an insert execute query to the specified table, This function does not return a value, however the printQuery can be set to True to view the query that is inserted
columnNamesList = ['name', 'age', 'location']
listOfListValues = [
['Daivd','45','Detroit'],
['Dora','45','Null']
]

p2sql.putInTable(connection,'TestTable2',columnNamesList, listOfListValues,'databaseTest2')

getFromTable()
getFromTable(
connection(), t
ablename: str,Database="",
printQuery = False,
listOfColumnNames = [ '' ] ,
whereClause = [[]] ,
schema='dbo'
)

getFromTable() will run a select query against the conneciotn and return a list of tuples from the specified table
Example:
[('Daivd', 45, 'Detroit'), ('Mike', 45, 'Null')]

p2sql.getFromTable(connection,'TestTable2','databaseTest2')

merge()
merge(
values: list, *example: [[]]
columns:list,
targetTable: str,
matchOnSource : list,
matchOnTarget : list,
WHEN_MATCHED_THEN_UPDATE_target = True,
NOT_MATCHED_BY_SOURCE_DELETE = True,
NOT_MATCHED_BY_SOURCE_query = "",
printQuery = False
)

merge() creates a MERGE query, which combines the INSERT, UPDATE, and the DELETE operations altogether, Creates a tempTable from the supplied lists
columns = ['name', 'age', 'location']

values = [
['Big Bird', '32', 'Seasame Street'],
['Dora', '5', 'The Void']
]

Note: you can also use pandas DataFrame by using tolist() on df values Example:
values = df.values.tolist()
columns = df.columns.tolist()

mergeQuery = merge(values,columns,'TestTable',['name'],['name'])

The merge() function will return a MERGE query
with DF_SQL_TBL_8675 as ( select * from(
VALUES ('Big Bird','32','Seasame Street'),('Dora','5','The Void')) tempTable ([name],[age],[location])
)

MERGE TestTable AS Target
USING DF_SQL_TBL_8675 AS Source
ON Source.[name] = Target.[name]

-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT ([name],[age],[location])
VALUES ( Source.[name] , Source.[age] , Source.[location] )

-- For Updates
WHEN MATCHED THEN UPDATE SET
Target.[name] = Source.[name] , Target.[age] = Source.[age] , Target.[location] = Source.[location]
-- For Deletes
WHEN NOT MATCHED BY Source THEN DELETE;

Pandas DataFrame Functions
The following functions will convert pandas DataFrames to SQL queries.
import pandas as pd

df_createTblQuery()
df_createTblQuery( database: str, tablename: str, dataframe: pandas.DataFrame ,printQuery: bool, schema='dbo' )

df_createTblQuery(), takes the column headers from a pandas DataFrame and creates a SQL create Table query.
Note: that this query will not insert data into the created table even if the DataFrame contains data for that you would need to use df_insertQuery(). All of the datatypes will be of type NVARCHAR(MAX)

d = {'name': ["Big Bird", "Dora"], 'age': ['32', '5'], 'location': ['Seasame Street', 'The Void'] }

df = pd.DataFrame(data=d)

dataframeCreateQuery = p2sql.df_createTblQuery('databaseTest2','df_TestTable',df)

p2sql.executeQueries(connection,[dataframeCreateQuery],True)

df_insertQuery()
df_insertQuery(
database: str,
tablename: str,
dataframe: pandas.DataFrame,
printQuery= False,
schema='dbo'
)

df_insertQuery() will itterate through pandas dataframe and insert data into a sql database table.
Note that the DataFrame column namnes need to match the database column names.
d = {'name': ["Big Bird", "Dora"], 'age': ['32', '5'], 'location': ['Seasame Street', 'The Void'] }

df = pd.DataFrame(data=d)

dataframeQuery = p2sql.df_insertQuery('databaseTest2','df_TestTable',df)

results of variable dataframeQuery:
INSERT INTO [databaseTest2].[dbo].[TestTable2](name , age , location )VALUES('Big Bird','32','Seasame Street');
INSERT INTO [databaseTest2].[dbo].[TestTable2](name , age , location )VALUES('Dora','5','The Void');

p2sql.executeQueries(connection,[dataframeQuery],True)

p2sql.getFromTable(connection,"df_TestTable","databaseTest2",True)

df_mergeQueryTables()
df_mergeQuery(
database: str,
targetTable: str,
sourceTable: str,
target_source_columns: list,
dataframe: pandas.DataFrame,
mergeDelete: bool,
deleteStatement: str,
schema='dbo'
)

df_mergeQueryTables() creates a merge query, merges data from source table to target table. as long as the supplied dataframe matches the source and target table.
WHEN target table ON target_source_columns NOT MATCHED BY TARGET
then insert source table rows with insert query

WHEN source table column MATCHED target
then update target table with update query

WHEN NOT MATCHED BY SOURCE
THEN DELETE

Unless mergeDelete is set to True,
then set deleteStatement to whatever query you want to run when not matched by source

df_mergeQueryTables = p2sql.df_mergeQuery("databaseTest2","df_TestTable","TestTable2",['name'],df,True,'')

p2sql.executeQueries(connection,[df_mergeQuery])