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!

========================================

What to watch next:
PostgreSQL Stored Procedures
SQL Procedures Automation

========================================

Resources for the tutorial:

SQLAlchemy Documentation on setting up the engine configuration:

XML Spreadsheet for the table of housing prices:

========================================
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:
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)
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)

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!
LinkedIn
Medium

========================================

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:

========================================

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.