r/learnpython • • May 13 '21

Sharing my win.

I work in global supply chain and logistics for a project management group of a global apparel company. I’m considered the tech guy on the team and the data nerd. I manage projects and handle all reporting, automation, etc. pretty fun stuff.

Well, today I did something that saved us 12 hrs of delay in execution in our supply chain by writing a script that pulls data from GCP out to an oracle db and then execute sequential sql statements to transform the data and refresh tables that feed tableau dashboards. It all now takes less than 20 minutes via a batch file in the background.

Millions of rows of data for three specific tables, tons of calculations, etc.

Python confidence is way up folks and it’s due to the knowledge sharing that is to be had in this group and others dedicated to the art of Python.

Felt like I should share my lessons learned.

  1. I need to get better at testing

  2. CLOB sucks so utilize sqlalchemy types for varchar

  3. Tnsnames are not hard

  4. 64 bit memory usage trumps 32 bit on large datasets

  5. Chunk size is a godsend.

Cheers to you.

415 Upvotes

48 comments sorted by

9

u/10a00001010 May 13 '21

I am currently taking up python courses heading towards data analysis. This is such an inspiration. Such job sounds really fun!

11

u/Brothercford May 13 '21

I fell into this job. I was working as a maintenance lead for a team of 8 techs in one of our DCs and applied for a project manager role in corporate and impressed the director enough to sit down and create a role for me and that was initially building access database stuff, project support, and basic analysis.

Then I just poured myself into it. All of it. Small team forced me to be scrappy which python perfectly fits into.

7

u/jjjohhn May 13 '21

Hey I work in the same area, so I’m curious to see your script if possible? You’d probably need to anonymise some bits I guess, but it would be great.

Also quick question - why are you transforming the data in oracle, could it be done in Python with pandas instead of using sql?

5

u/Brothercford May 13 '21

I’ll see what I can do.

The oracle scripts were already developed by a separate team and were being ran manually with toad from India. There was basically being a three way shake to accomplish the work so this was quickest way to end state.

2

u/IamFromNigeria May 14 '21

Do you mind sharing the python script

1

u/Brothercford May 14 '21

I have to clean it up to protect IP but sure will share it here.

2

u/coder155ml May 14 '21

If you’re worried about protecting IP I just wouldn’t share it. It’s company property. It isn’t your personal project.

1

u/Brothercford May 14 '21

I can strip out the queries is what I mean. The frame of it is common knowledge

3

u/jjjohhn May 13 '21

Ok yea that makes sense, cool thanks for that!

4

u/OhhhhhSHNAP May 13 '21

So are you gonna tell anyone or just keep posting on Reddit and acting busy?

4

u/Brothercford May 13 '21

Tell anyone what? Hahaha acting busy, I love it.

2

u/[deleted] May 13 '21

as an absolute beginner joining this sub to learn I understand nothing in this post hahaha but hopefully one day I can write something similar!

4

u/Brothercford May 13 '21

You can do it. Find a problem you want to solve and don’t stop googling until you can solve it. Immersion is the best medicine.

2

u/manihateitherebro May 13 '21

What modules/dictionaries did you use? To build it

4

u/Brothercford May 13 '21

I am using:

Pyodbc to connect to GCP

Pandas to to_csv and read_sql_query

Time to track how long it takes

Sqlalchemy’s create_engine and types the first to push the data frame to oracle staging table and the second to force varchar and avoid CLOB insertion.

Cx_Oracle for executing the previously developed sql scripts

Numpy for arrays

Datetime to get dates

Obviously I’m using two separate odbc connector libraries as this was developed over time in pieces and brought together and also oracles library…Improvement over time. Get it working first. Executes fully in 24 minutes.

2

u/[deleted] May 13 '21

Nice man. I also used to work in supply chain then moved into data engineering.

Sounds like it might be a good path for you also.

2

u/Brothercford May 13 '21

I’m trying to find where I fit in to be honest. I wear two hats right now project manager and data whatever.

I’m open to advice etc.

2

u/Brothercford May 13 '21

Currently I’m starting a program to get me ready to sit for the PMI-ACP. My boss is doing the pmp and then we intend to swap information and do the opposite. It’s not funded by us so we’re taking the free offer.

2

u/hcabbos70 May 13 '21

What was/is your organization’s reaction? Boss wo/man gotta be happy 😁

2

u/Brothercford May 13 '21

Furthermore I’m a coordinator by title if it matters but yeah.

2

u/pacmanddd May 14 '21

You should rephrase your job title in the resume to suit what you do in your current role. And if needed, you can justify your reponsibilities and work in the interviews.

2

u/Brothercford May 13 '21

I’ll be honest, this is just one of many things I’ve done over the past three years in my role and I’ve asked to be compensated and titled accordingly to no avail. So all the amazing performance reviews don’t equate to anything.

Just the other day a resume reviewer said my resume looked faked because I don’t have a degree or certifications and the accomplishments looked too big for the resume.

So what do ya do?

2

u/hcabbos70 May 14 '21

Money isn’t everything, but I trust your future will land a great job offer that will take into account your experience. Just be you. Life has a way of working itself out.

2

u/open_risk May 14 '21

20 minutes sounds quite a lot :-) How big is the dataset and where is the bottleneck (network, db/disk i/o, compute?). Next task: bring it down to 2min :-) :-). If its compute time i'd focus on the pandas bit...

Just kidding you, great to share examples of solving real problems using python. Speed is just one dimension btw, increasing confidence in the process is important too (documenting data quality, informative good looking reports etc.) and python has plenty of options on that front as well

1

u/Brothercford May 14 '21

I’d love to get it down to much quicker but the crunch is one of the calculation steps in the backend and the insertion of the rows into the staging tables. As for row counts the largest table averages 1.2m records that are 30 columns long.

77

u/arrarat May 13 '21

I get the feeling this surpasses r/learnpython but I get that from a lot of other posts too.

40

u/Diggy696 May 13 '21

10

u/Boogzcorp May 13 '21

Really? I was more like this

6

u/GIPPINSNIPPINS May 13 '21

Both of these 😂

2

u/arrarat May 14 '21

Glad I'm at least not alone in this xD keep on hopin folks...

1

u/GIPPINSNIPPINS May 14 '21

Have you heard of matplotlib? I just started trying it on data camp and its super neat. Like crazy how it can just plot things really well.

4

u/skellious May 14 '21

/r/learnpython is a broad church. we welcome everyone, no matter their stage in their python journey :)

4

u/mazamorac May 13 '21

Nice! It's good to bask in success, great to recount lessons learned.

4

u/vanTrottel May 13 '21

I worked at Bosch in germany for half a year as part of University and developed the queries for the dashboards, we used Power BI. So I got very deep into our supply chains. This part alone Was so complicated that we needed multiple people for this, so I can imagine what u achieved. Great job :)

1

u/eerilyweird May 13 '21

Just curious: What does “supply chain” mean here? Feeding dashboards is cool but I don’t know if I’m supposed to think of engineering a dashboard as a supply chain?

1

u/Brothercford May 13 '21

Well this dashboard helps drive an inventory optimization model that reduces lead times in the supply chain, right product right time, pre staging fabrics and setting up min max or WOS/dos targets to operate on at a sku level (lowest level of the inventory).

1

u/Brothercford May 13 '21

As for the specific 12 hrs. It’s due to resources. My counterpart is in India. Running this outside of our erp requires moving data around and at first I was running the first half of the scripts manually on my end and then they would run their side when they came in the office the problem was there is a time difference that left us with a 12 hr gap basically.

2

u/Nostalien May 13 '21

+1 for chunk size. It’s a good skill to have.

2

u/Visible-Income-7301 May 14 '21

That is awesome!! I am in a similar boat. I am an operations manager and have taken on analyzing data and building reports. At first using excel with vba macros, which then turned into learning python(and any other tech that I stumble apon because ADHD :P) to now going back to school to get a IST degree because I love building programs and working with data so much. I hope to one day be posting my success story on this group like you!

Cheers!

33

u/[deleted] May 13 '21

[removed] — view removed comment

6

u/Brothercford May 13 '21

That’s a nice use of customer sentiment analysis automation!

2

u/[deleted] May 14 '21

[removed] — view removed comment

1

u/Brothercford May 14 '21

Don’t even consider myself google worthy

2

u/5paceNinja May 14 '21

I highly doubt that. You've seen the trash Google tries to pass for production right?

1

u/Brothercford May 14 '21

I’ll look into it as I have not.