Pandas SQL - How to read data from a microsoft sql database

Reading data into pandas from a sql server database is very important. If you plan on working for a company you HAVE TO know how to use Pandas and SQL. This video will show you how.

What is Pandas? A Python data analysis library

Most big companies either use Microsoft or Oracle for their RDBMS (relational database management system). There are others like mysql but if you learn these two, you should be covered in most cases. This video will concentrate on getting data out of a Microsoft database.

We first connect to sql server and then open the database called BizIntel. This is where you will find a table called "data". Note this is all made up data created for the purposes of this tutorial.

The table contains three columns: Date, Symbol, and Volume
This table simulates stock prices and their volume on a specific date.

We then turn to the Jupyter notebook and start to import SQL specific Python libraries like PyODBC. We then proceed with explaining code on how to connect to your local mssql (microsoft sql server) instance via Pandas and PyODBC. This is how we go to pandas from sql.

We finally generate the sql statement for pandas and read in the data. This is a very basic example and we did not have to supply the odbc connection any passwords. We also did not have to worry about changing any of the datatypes of our dataframe as they were imported into pandas without any issues.

[Gabicus]

Excellent Tutorial. This code worded for me without any iterations. I searched multiple forums for an easy way to connect to MS SQL Server, and I ran into errors all over the place. Thanks for putting this together!!

[SamBernickTaylor]

Thanks so much for this video!! I have been banging my head against a wall all week trying to connect to my companies MS SQL Server database and this video held the answer that finally worked correctly.

[mfteng]

Thanks for the nice video, easy to follow.

[ruby_gleyzes]

Thank you for your tutorial, it helped a lot!

[loveyoutube]

Thank you so much. This is very useful. Thank you, thank you !!!

[moaiedbetamour]

Thank you for your video, worked perfect.

[alfonxox]

Thanks a lot, i spend a lot of time with another bad videos, you are amazing

[chandanroy]

Thanks very much !

Just wanted to know:

how can we set ApplicationIntent=ReadOnly while connecting to ms sql db ?

[ml]

Really nice video! Just a question: In case that I need to read more than one table into python; which would be the best method to do it without declaring everytime the variable(df1, df2, df3, ...) in which the dataframe should be stored? Thanks!, ML

[ferantoteam]

great pal, got memory error because my database is huge, could tell how to chunk or another method or using dask?

[prathyushakommuru]

I want to run a query in which tables from two different database are to be joined.How can i do that?

[SaadMahmood]

Hey, I've been trying to get python to connect to SQL Server 2016, I keep getting this error: "[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". My Python is 32 bit and the ODBC Driver I'm using for MSSQL is also 32 bit, I've tried making System DSNs and User DSNs, and nothing seems to work!

[eikm]

How do you query if you have columns with spaces like first name I have tried [first name] and `first name` and it does not work. I appreciate this info

[jeannieramirez]

i get an error that NameError Traceback (most recent call last)
in <module>
4
5 # Create the connection
----> 6 conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + DB['servername'] + ';DATABASE=' + DB['database'] + ';Trusted_Connection=yes')
7
8 # query db

NameError: name 'DB' is not defined

[jonathanrosenfeld]

Can you please also show how to write DataFrame into MS SQL server?

[CentroCoronelAprende]

No module named 'pyodbc' any idea?

[wenchi]

I got this message....

TypeError Traceback (most recent call last)
in <module>()
4
5 # Create the connection
----> 6 conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + db['servername'] + ';DATABASE=' + db['database'] + ';Trusted_Connection=yes')
7
8 # query db

TypeError: string indices must be integers

[aymanhamza]

Hi, thanks for that, but is there a way to do the exact same thing to MS access database?