How to Use SQL with Excel using Python



In this video you will learn how to use SQL with excel using python. Use sql with excel with python and learn to automate your excel tasks.

👉 Patreon: patreon.com/SATSifaction
👉 Facebook Group: http://facebook.com/groups/theaiwarriors
👉 Instagram: @theaiwarriors
👉 Corporate Training and Up skilling: https://levers.ai

Netfirms (Affiliate) – https://bit.ly/2KdJ4Dp
Linode Server – https://bit.ly/2XpqGi9
Bluehost (Affiliate) – https://bit.ly/2GxxBh1
PythonAnywhere (Affiliate) – https://bit.ly/2kWORVe
Heroku – https://www.heroku.co
NordVPN (Affiliate) – https://bit.ly/2W87je0

Here is a link to my python for beginners, master python course: https://bit.ly/2HIZS42

What Does a Data Science Executive Carry Around in 2019?

Bag – https://amzn.to/2HfHkJo (will fit laptop plus one tablet and a few accessories)
Laptop – 2018 MacBook Pro with Touch Bar
Tablet – 2017 9.7” iPad or
Tablet – Amazon Fire HD 10 – https://amzn.to/2HjeLec
Folding Keyboard – https://amzn.to/2JLMbE7
Power Bank – https://amzn.to/2VsNASc
Bose QC35 – https://amzn.to/2LEpqEx

Home Setup for more intense analysis and editing:

AMD MSI Vega 56 Graphics Card – https://amzn.to/2HkiJU1
Razor X Core egpu with thunderbolt 3 – https://amzn.to/2Hix579
Two LG 29” Ultrawide Monitors – https://amzn.to/2YtZfSi

Other options to run Python on a budget

Raspberry Pi Starters kit – https://amzn.to/2Q4lzPE
Any inexpensive Android Tablet (all Fire tablets with playstore side loaded)
Minimalist Keyboard and Mouse – https://amzn.to/2Q2w2ek

Favourite Mobile Apps to Run Python and Code
iOS – Pythonista (paid) – https://apple.co/2HjRVTJ
Android – Dcoder (free) – https://bit.ly/2Vrjb6N

Music:

Finally by Loxbeats https://soundcloud.com/loxbeats
Creative Commons — Attribution 3.0 Unported — CC BY 3.0
Free Download: http://bit.ly/FinallyLoxbeats
Music promoted by Audio Library https://youtu.be/fGquX0Te1Yo

source

45 thoughts on “How to Use SQL with Excel using Python”
  1. Hi, i still dont see the point on working wit Python if i already use SQL server and PowerBi for extracting, procesing data and creating dashboards.. what im missing? :/

  2. This is wonderful. Although all could be done with pandas. It's interesting for those who already master SQL. 😊

  3. Thank you so much! This is exactly what I've been looking for for a long time. I've never found a good explanation of how to get a preexisting dataset into a database or SQL environment, but this skips that entirely so I can get started practicing SQL!

  4. Thanks for the KT, it's very helpful. But I have one question, I'm getting error if column name has space like 'bolo name'. Could you please help on this

  5. Instead of creating a new Excel file for output , how may I add output to an existing sheet or to new sheet of same excel?

  6. Hi, firstly thanks for the video, really useful. A question; do you find that it is more common for the approach to be used (i.e. querying a dataset via SQL w/sqlalchemy & sqllite) than reading the file into pandas, massaging the data there (so df.loc[conditions,columns] for example, which would do a similar thing) and then exporting it to a SQL database? I ask this because this is instinctively what I would choose to use the latter approach rather than run a SQL query there.

  7. Use case : I have 100 SQL queries stored in an excel file which needs to be executed in AWS Console or any DB query editor.

    I need a python script for the same, which would read the queries from the excel file , execute them one after another and store the output in some format ( excel, email, text anything )

    Can anyone help me out with the script if they have it handy.

    mail id : rajesh.sahoo17@gmail.com

  8. Thanks! This was very informative. I've worked with Excel and SQL Server for years and working with Python in the last few years. This beats VB hands down. As I saw one person post, Python is the glue that brings apps together.

  9. That's very good job
    But what if I want to do calculation on the data instead of filtering it
    Can you make a tutorial for that as well 😁

  10. as a beginner looking to utilize SQL and python to manage large excel data, where should I start? it seems like you are linking this to SQL and subsequently using python language to manage the data – hope i understand it currently.

  11. What if you want to display only certain columns instead of all columns, it is not working for me

  12. ————————————————————————–

    I am getting following error before creating data frame.

    NameError Traceback (most recent call last)

    <ipython-input-8-d9211fa18ab8> in <module>

    —-> 1 engine = create_engine('sqlite://', echo=False)

    2 #df=pd.read_excel(file, sheet_name='AccountMasterData.xlsx')

    NameError: name 'create_engine' is not defined

  13. I am sorry if I miss it, but is there a way to get the sample excel file (Employee-Attrition.xlsx)? Thanks

  14. Great Vid! Can you get the data from multiple sheets or excel files and use JOIN in the SQL? If so, that would be very useful!

  15. Please do more of these videos with SQL wildcards! Maybe finding only valid emails * food for thoughts

  16. Hi thanks for the video it was amazing. I was hoping you could help I am trying to pull all columns that are between 2 date ranges I am using the following: results= engine.execute("SELECT * FROM consults WHERE Appointment Start Date Between '2-1-20' AND 1-31-21' ") this is the format of the column 10/14/19 08:30

  17. Going to try this today. My only challenge at work is, it's hard to import pandas from PIP. Firewall blocks these.

  18. If you want to advance your career then you have to learn these languages. Every person regardless of your discipline needs to learn SQL, Python and basic excel formulas and more importantly Excel navigation. Saying that though someone in a few years or if they haven't already will come up with a way to this in a drag and drop format avoiding some of the technicality ie Rows or Glide.

  19. Just awesome..but one question..why are we importing sqlite3..it is not used anywhere in the code..and my code also gives me warning about not using sqlite3 package..

  20. I know I'm late to this but here's a couple of things I discovered that might be of use to others. 1) The current version of xlrd (which is a pandas requirement) will only work with .xls files – not .xlsx. To overcome this pip install openpyxl and in the .read_excel line add engine='openpyxl'. 2) This example assumes your header rows will always be on line one. If they're on some other row, also in the read_excel line add header=n (where n equals the row where the headers actually are NOTE: this is 0 indexed so if your headers are on row 4 in the Excel, set headers=3 in your code. 3) Headers with characters that are normally invalid in SQL column names can be an issue but you can take care of most of them by add this line AFTER the read_excel line – df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_').str.replace('(', '').str.replace(')', '') This will fix most, but you can add to this if needed. Now you may still end up with other invalid characters in column names such as (.) or (#) – to overcome that, wrap your those column names in brackets [grower_#] or [transfer.1].

    Now, I have a question that hopefully someone else can help me with. Anytime I try to select anything but * from the .to_sql table I get errors such as (I'm trying to select only 2 columns):
    Traceback (most recent call last):
    File "C:lee_devtest_sqlite01.py", line 18, in <module>
    final = pandas.DataFrame(results, columns=df.columns)
    File "C:UsersleeAppDataRoamingPythonPython36site-packagespandascoreframe.py", line 509, in _init_
    arrays, columns = to_arrays(data, columns, dtype=dtype)
    File "C:UsersleeAppDataRoamingPythonPython36site-packagespandascoreinternalsconstruction.py", line 548, in to_arrays
    return _list_to_arrays(data, columns, coerce_float=coerce_float, dtype=dtype)
    File "C:UsersleeAppDataRoamingPythonPython36site-packagespandascoreinternalsconstruction.py", line 567, in _list_to_arrays
    raise ValueError(e) from e
    ValueError: 14 columns passed, passed data had 2 columns

  21. Best use case video for Python/Excel/SQL I've seen and really well explained! Subscribed and now watching everything… Awesome Job!

Leave a Reply

Your email address will not be published.

Captcha loading...