Python Database Connectivity: SQLite to BigQuery Without Hardcoding Passwords

Analysts who can only query inside a UI are bottlenecked. This guide makes your notebook the client — SQLite locally, then Postgres/MySQL/Snowflake/BigQuery with the same pd.read_sql call, all without hardcoding a password.

What is the single pattern you memorise?

One engine string per dialect, one pd.read_sql to read, and secrets from .env via python-dotenv. Every dialect section follows the same three lines so you only swap the string.

How do you stop hardcoding passwords?

Setup:

# !pip install pandas sqlalchemy psycopg2-binary pymysql mysql-connector-python snowflake-connector-python google-cloud-bigquery duckdb pyodbc python-dotenv
import os
from dotenv import load_dotenv
load_dotenv()  # reads .env in this folder
# create .env with: DB_PASSWORD=my_secret_password
pwd = os.getenv('DB_PASSWORD')  # None if missing — fail loudly
print("Env loaded:", pwd is not None)

Add .env to .gitignore. No password ever appears in the committed notebook — a hiring-manager hygiene check.

How do you connect to SQLite (zero setup)?

SQLite is a file; no server needed.

import sqlite3
import pandas as pd
 
conn = sqlite3.connect('example_local_db.sqlite')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS sales (id INTEGER, amount REAL)")
cursor.execute("INSERT INTO sales VALUES (1, 100.50), (2, 200.00)")
conn.commit()
 
df = pd.read_sql("SELECT * FROM sales", conn)
print("--- Data from SQLite ---")
print(df)
conn.close()

Rendered output: a 2-row DataFrame id | amount with 100.5 / 200.0, proving pd.read_sql works on a DB-API connection identical to engines below.

How do you connect to Postgres, MySQL, Snowflake, and BigQuery?

Same call, different engine string.

from sqlalchemy import create_engine
 
# PostgreSQL: postgresql+psycopg2://user:pass@host:port/db
DB_USER='your_username'; DB_PASS=os.getenv('POSTGRES_PASS','demo')
DB_HOST='localhost'; DB_PORT='5432'; DB_NAME='analytics_db'
pg_str = f'postgresql+psycopg2://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}'
engine_pg = create_engine(pg_str)
# df_pg = pd.read_sql("SELECT * FROM employees LIMIT 10", engine_pg)
 
# MySQL: mysql+pymysql://user:pass@host:port/db
mysql_str = 'mysql+pymysql://root:password123@localhost:3306/my_database'
# engine_mysql = create_engine(mysql_str)
# df_mysql = pd.read_sql("SELECT * FROM orders", engine_mysql)

For Google BigQuery, use the native client that returns a DataFrame directly:

# from google.cloud import bigquery
# client = bigquery.Client()
# query = "SELECT * FROM `project.dataset.table` LIMIT 10"
# df_bq = client.query(query).to_dataframe()
# print(df_bq.head())

Write path (all dialects via SQLAlchemy):

# df.to_sql('my_table', engine_pg, if_exists='replace', index=False)
# print("Written to Postgres")

How do you layer this into an analyst stack?

Pull via API per API Masterclass -> normalise to DataFrame -> to_sql into Postgres -> query back with read_sql for Pandas shaping. Keep SQL in the database (filter, join) and Pandas for wrangling that SQL does not do well, per SQL vs NoSQL.

Download the Notebook and Practise

This article is a walkthrough of a runnable Jupyter notebook. Download the original .ipynb and run it locally or on Colab — every code block above appears in order.

Continue your track: Data Analyst Roadmap · Python and Pandas Guide · SQL NULL Handbook · SQL JOIN Fan-Out · Topfolio Practice · Data Analyst vs Engineer

Dataset generators where applicable are in courses/workbooks/generators/ — see citations atop for the exact *.py source for this notebook.

Frequently Asked Questions

Why use SQLAlchemy instead of raw DB drivers?

SQLAlchemy provides a single create_engine string per dialect so pd.read_sql works identically for SQLite, Postgres, MySQL, and Snowflake — you only swap the connection string.

How do you avoid hardcoding passwords in notebooks?

Store secrets in a .env file and load with from dotenv import load_dotenv; os.getenv('DB_PASSWORD'). Never commit .env — add it to .gitignore.

Why does pd.read_sql need a connection or engine?

pandas delegates execution to the DB-API/SQLAlchemy connection; the engine manages pooling and dialect translation so you stay in DataFrame-land.

Which library for BigQuery from Python?

google-cloud-bigquery plus SQLAlchemy or direct client.query(sql).to_dataframe(). Authenticate via service-account JSON and set GOOGLE_APPLICATION_CREDENTIALS.