Machine Learning in SQL? But I Am Python Expert Part 1 | SQLAlchemy Tutorial | PostgreSQL + Pandas

Machine Learning in SQL is not only possible but is not too difficult if we think about what we know, i.e., where we are and where we want to go. If you know how to work with machine learning in Python, Pandas dataframes, etc. then why don't we convert this question of machine learning in SQL into machine learning in Python? The only question is how to do it. Fear not, my friends, because I will show you how and explain everything step-by-step. How to create an SQLAlchemy engine, connect to our local database, and fetch the database table into a pandas data frame (and vice versa). As the tutorial became a little bit too big for a single video, I divided it into several parts. This is Part I - SQLAlchemy overview and all the preparation work that needs to be done before we can move to ML stuff. There are many things to cover but I will go over one thing at a time. Here is a Timeline if you want to explore a specific part. ======================================== Timeline 00:00 Intro: Why ML In SQL? 03:04 Preparations: Python Libraries 03:27 Library Imports (and installation) 04:20 Why dotenv library? 04:58 Good Practice For Secrets 05:02 Why psycopg2? 05:59 Understand SQLAlchemy API 06:38 Understand SQL Dialect 07:04 Syntax Of SQLAlchemy 07:30 Custom Method For DB Connection 08:38 Time To Connect Using Our Credentials! 09:29 Deadly Combo: Pandas + SQLAlchemy 11:32 Getting Sample Table For ML! 13:23 How To Save DataFrame As Database Table? 13:39 Double Check Our New DB Table! ======================================== Resources for the tutorial: SQLAlchemy Documentation on setting up the engine configuration: https://docs.sqlalchemy.org/en/20/core/engines.html#postgresql XML Spreadsheet for the table of housing prices: https://docs.google.com/spreadsheets/d/1caaR9pT24GNmq3rDQpMiIMJrmiTGarbs/edit#gid=1150341366 ======================================== Queries from the tutorial: # Custom function for initializing the SQLAlchemy engine def connect_to_db(user, password, host, port, database): # for creating a connection string connection_str = f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}" # SQLAlchemy engine engine = create_engine(connection_str) # You can test if the connection is made or not try: with engine.connect() as connection_str: print('Successfully connected to the PostgreSQL database') except Exception as ex: print(f'Sorry failed to connect: {ex}') return engine # Imports from sqlalchemy import create_engine # to install pip3 install sqlalchemy import psycopg2 # to install pip3 install psycopg2-binary import pandas as pd # to install pip3 install pandas from dotenv import load_dotenv # to install pip3 install python-dotenv import os # Load environment variables from .env load_dotenv() # for postgreSQL database credentials can be written as user = 'postgres' # read from environment file (.env) password = os.getenv('SECRET_PASSWORD') host = 'localhost' # or IP 127.0.0.1 port = '5432' database = 'postgres' engine = connect_to_db(user=user, password=password, host=host, port=port, database=database) df_user_info = pd.read_sql('SELECT * FROM public.invoices;', con=engine) df_user_info.head() The rest, as they say, is the history. If you have any troubles, questions, or suggestions, drop me a comment. I am always happy to hear from you! Subscribe for more. ======================================== Ways to connect: Subscribe! https://bit.ly/RBrilenkovYT LinkedIn linkedin.com/in/ruslan-brilenkov Medium ruslan-brilenkov.medium.com ======================================== Disclaimer 1: This royalty-free background music was generated with AI software and post-processed afterward. If you are tired of spending too much time and energy finding background music for your videos, dramatically reduce that time with Mix.audio! Use the promo link below to get a 30% discount on your first monthly subscription payment, and by doing so, you will also support the channel at no extra cost to you: https://bit.ly/ruslanpromoAI ======================================== Disclaimer 2: everything presented in this video is my own opinion and is meant to educate and share information, nothing mentioned or described here is legal or financial advice. Ruslan Brilenkov is not responsible for any profits or losses associated with your investment. So, please be responsible for your own actions.