jamescalam

import sys
sys.path.insert(0, r'C:\\User\medium\pysqlplus\lib')
import os
from data import Sql

sql = Sql('database123')  # initialise the Sql object

directory = r'C:\\User\medium\data\\'  # this is where our generic data is stored

file_list = os.listdir(directory)  # get a list of all files

jamescalam

def union(self, table_list, name="union", join="UNION"):
    """Pass a list of table names to union them all together. The join
    argument can be changed to alter between UNION/UNION ALL.

    Keyword arguments:
    table_list -- a list of table names, example: to union [d1] and
                  [d2], table_list = ["d1", "d2"]
    name -- the name of the table created by the union (default "union")
    join -- the union type, either "UNION" or "UNION ALL" (default "UNION")
    """

jamescalam

def union(self, table_list, name="union", join="UNION"):
    
    # initialise the query
    query = "SELECT * INTO ["+name+"] FROM (\n"

    # build the SQL query
    query += f'\n{join}\n'.join(
                        [f'SELECT [{x}].* FROM [{x}]' for x in table_list]
                        )

jamescalam

def manual(self, query, response=False, comment="manual query",
           verbose=False):
    """Enter a manual statement/query.

    Keyword arguments:
    query -- SQL query to run on SQL connection
    response -- Boolean value stating whether a response/table
                should be returned (default False)
    comment -- string input that translates into a comment in the
               self.query string (default "manual query")

jamescalam

def manual(self, query, response=False):
    cursor = self.cnxn.cursor()  # create execution cursor

    if response:
        return read_sql(query, self.cnxn)  # get sql query output to dataframe
    try:
        cursor.execute(query)  # execute
    except pyodbc.ProgrammingError as error:
        print("Warning:\n{}".format(error))  # print error as a warning

jamescalam

def drop(self, tables):
    """Pass a table or list of table names to drop.

    Keyword arguments:
    tables -- a single table name as a string, or a list of table names as
              strings. For [dbo].[data] we would input "data"
    """
    # check if single or list
    if isinstance(tables, str):
        # if single string, convert to single item in list for for-loop

jamescalam

def drop(self, tables):

    # check if single or list
    if isinstance(tables, str):
        # if single string, convert to single item in list for for-loop
        tables = [tables]

    for table in tables:
        # check for pre-existing table and delete if present
        query = ("IF OBJECT_ID ('["+table+"]', 'U') IS NOT NULL "

jamescalam

def push_dataframe_dtypes(self, data, table="raw_data", batchsize=500,
                          overwrite=False, fast_upload=False):
    # if overwrite is true we auto remove any tables with same name
    if overwrite:
        # check for pre-existing table and delete if present
        self.drop(table)

    # convert pyodbc connection string into sqlalchemy friendly format
    connection_str = urllib.parse.quote_plus(self.connection_str)

jamescalam

import numpy as np
import matplotlib.pyplot as plt
from matplotlib import animation
from mpl_toolkits.mplot3d import Axes3D


# create cluster builder
def cluster(center, radius=10, n=50):
    c = np.asarray(center)
    xx, yy, zz = np.random.uniform(c-radius, c+radius, size=(n, 3)).T

jamescalam

import numpy as np
import matplotlib.pyplot as plt
from matplotlib import animation

# figure and axes setup
fig = plt.figure(figsize=(12, 6))
ax = plt.axes(xlim=(-0.25, 2), ylim=(-1.2, 1.2))

sine, = ax.plot([], [], linewidth=4, color='#212B38')