Master Using SQL with Python: Lesson 3 - Using Enterprise Databases with ODBC

In this third part of the series, you learn how to query production scale database systems like SQL Server, Oracle, PostgreSQL, and MySQL using pyodbc and Pandas. This takes a bit more work than the prior methods but gives the most power and flexibility.

Join my Patreon Community and Watch this Video without Ads!

Twitter: @BryanCafferky

Notebook and Data

[normalizethis]

Did you say "Bing it" instead of "Google it"? Wow you really did work for Microsoft.

[justinmichlig]

Thank you Brian for an overview on the ways we can mesh python and SQL. I need to learn a lot more on SQL basics, but like how you presented the capabilities in a manner that someone isn't a SQL master could find the process useful.

[fschadeck]

Excellent content Bryan. Exactly what I needed in order to get my project going! Kudos to you!

[Spida]

Great topic. I will try to implement the windows authentication in all my automated analytics/processes!

[TheSergey]

Thanks a lot. Well structured information and easy to understand. I looking forward to seeing other videos from you.

[krishnakuraku]

thank you Bryan..please upload more videos

[normalizethis]

I was able to use this video to connect and start running queries and I'm very new to python, so thank you!

[thiernoba]

Great content. How do i get the notebooks?

[imvivran]

Thank you Bryan for posting the video series. I have been following up till you started procedure to connect to SQL server. When I am trying to replicate your codes, I am getting following error:

('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I have tried all possible options of Driver: ODBC Driver 17 for SQL Server, ODBC Driver 13 for SQL Server, SQL Server, SQL Server Native Client 11.0 but all are returning same results.

Tried looking for solution on the web, but couldn't find any. Can you help?

[wadebouley]

Hello, I could really use some help. I'm trying to create a data frame that gets transformed to an excel spreadsheet (df.to_excel). I have everything that you have and it was correctly running yesterday, however, now it's taking pandas forever to run and it seems to not be working properly for it can no longer get through the entire script. It just runs and runs and run. Would you know how to fix this? I'd appreciate the help very much

[AutitsicDysexlia]

Where would you put things like Uncommitted Read, or TXNISOLATION in something like this? [DB2]

[TORMENTUMM]

I couldn't make work the ODBC driver while using ubuntu 18 :(

[roshunepp]

I'm just getting started trying to automate some reports. I'm trying what you've done here to connect to SQL server. I installed odbc for SQL 17 but when I create my connection, it says it's not there. Using vs2019.

[abhayprataprana]

var1 = '2'
var2 = 'A'

cursor.execute('''DELETE FROM add_section where CLASS_NAME='%s' and SECTION_NAME='%s' ''' %(var1, var2) )

Is it right??

[johnjenkins]

At this point (3 vids in) I decided to try and crib some of your code from the gitHub zip... After downloading I keep getting invalid zip. I tried on a different sys, but got the same. Likely my own ignorance but consider confirming the file is still valid. I ended up here because my pyodbc read from an iSeries db2 via DSN to pandas (pd.readsql) is now throwing the sqlalchemy warning. Tips welcome, but not expected as this is the wrong venue. (my odbc is using the iSeries Access ODBC Driver). Regardless thanks for taking the time to create these.

[cococnk]

🎉🎉🎉🎉 you are the best ( Dj Khaled is not !) 😊