turbodbc | Python module to access relational databases | Database library

I'm using Cloudera Hive ODBC driver in my code and I'm trying to containerize the app. Below is my Dockerfile,

I have a dask dataframe which has 220 partitions and 7 columns. I have imported this file from a bcp file as and completed some wrangling in dask. I then want to write this whole file to mssql using turboodbc. I connect to the DB as follows:

I am trying to test my dataflow pipeline on the DataflowRunner. My code always gets stuck at 1 hr 1min and says: The Dataflow appears to be stuck. When digging through the stack trace of the Dataflow stackdriver, I come across the error saying the Failed to install packages: failed to install workflow: exit status 1. I saw other stack overflow messages saying that this can be caused when pip packages are not compatible. This is causing my worker startup to always fail.

This is my current setup.py. Can someone please help me understand what I am missing. The job id is 2018-02-09_08_22_34-6196858167817670597.

setup.py

I wanted to install SQLAlchemy for Python 3 for working with databases.

I searched for the package using pip3 search SQLAlchemy, but I didn't find SQLAlchemy as part of the results.

Why don't SQLAlchemy show up in the output below, when the package is available on PyPI?

https://pypi.org/project/SQLAlchemy/

SQLAlchemy 1.3.15

I'm currently trying to tune the performance of a few of my scripts a little bit and it seems that the bottleneck is always the actual insert into the DB (=MSSQL) with the pandas to_sql function.

One factor which plays into this is mssql's parameter limit of 2100.

I establish my connection with sqlalchemy (with the mssql + pyodbc flavour):

I have a package that allows the user to use any one of 4 packages they want to connect to a database. It works great but I'm unhappy with the way I'm importing things.

I could simply import all the packages, but I don't want to do that in case the specific user doesn't ever need to use turbodbc for example:

I am trying to identify whether two values held in different numpy orderdict objects are the same.

Both dictionaries were created by using the fetchallnumpy() option in turbodbc and consist of two keys. First key is an id field the second key is a string value of variable length. I want to see whether the string value in the fist set of dictionary items, is present in the second set of dictionary items.

It's probably worth noting that both dictionary objects are holding approximately 60 million values under each key.

I've tried several things so far:-

1. np.isin(dict1[str_col],dict2[str_col])

   As a function but this was extremely slow, presumably because the string values are stored as dtype object.

2. I've tried converting both ditctionary objects to numpy arrays with an explicit string type as np.asarray(dict1[str_col], dtype='S500') and then tried to use the isin and in1d functions. At which point the system runs out of RAM. Have swapped out 'S500' to dtype=np.string_ but still get a MemoryError. (ar=np.concatenate((ar1,ar2))) whilst performing the isin function.

3. I also tried a for loop.

   [r in dict2[str_col] for r in dict1[str_col]]

   Again this was extremely slow.

My aim is to have a relatively quick way of testing the two string columns without running out of memory.

Additional Bits In the long run I'll be running more than one check as I'm trying to identify > new values and values that have changed.

Dictionary A = Current Data ['ID': [int,int,int]] Dictionary B = Historic Data ['record':[str,str,str]]

So the bits I'm interested in are :-

• A != B (current record is different to historic record)
• A not present in B (New record added to the database)
• B not present in A (Records need to be redacted)

The last two elements the quickest way I've found so far has been to pass the id columns to a function that contains the np.isin(arr1,arr2). Takes on average 15 seconds to compare the data.

I'm trying to create a temporary table in Microsoft SQL Server, then insert data into it, then return the data to Python as a dataframe, preferably.

Here is my connection, which works fine (password hidden).

I'm trying to generate and insert many (>1.000.000) Rows in a MS Access Database. For the generation I use numpy functions, therefore I try to access the database with python. I started with pyodbc: