Test Execute Script .SQL with sqlalchemy

README.md

test-sqlalchemy

python3.9 -m venv venv
source venv/bin/activate
#  pip freeze > requirements.txt
pip install -r requirements.txt

docker run --rm -it -e MYSQL_ROOT_PASSWORD=root \
                    -e MYSQL_DATABASE=reports \
                    -e MYSQL_USER=target \
                    -e MYSQL_PASSWORD=target \
                    -p 3306:3306 \
                    mysql:5.6

docker pull postgres:10.18-stretch
docker run --rm -it \
                    -e POSTGRES_DB=reports \
                    -e POSTGRES_USER=target \
                    -e POSTGRES_PASSWORD=target \
                    -p 5432:5432 \
                    postgres:10.18-stretch

query.sql

SELECT NOW() as now;
SELECT NOW() as now2;
SELECT NOW() as now3;

requirements.txt

argon2-cffi==21.3.0
argon2-cffi-bindings==21.2.0
asn1crypto==1.5.1
asttokens==2.0.5
attrs==21.4.0
backcall==0.2.0
beautifulsoup4==4.10.0
bleach==4.1.0
cffi==1.15.0
debugpy==1.6.0
decorator==5.1.1
defusedxml==0.7.1
entrypoints==0.4
executing==0.8.3
greenlet==1.1.2
ipykernel==6.10.0
ipython==8.2.0
ipython-genutils==0.2.0
jedi==0.18.1
Jinja2==3.1.1
jsonschema==4.4.0
jupyter-client==7.2.1
jupyter-core==4.9.2
jupyterlab-pygments==0.1.2
MarkupSafe==2.1.1
matplotlib-inline==0.1.3
mistune==0.8.4
nbclient==0.5.13
nbconvert==6.4.5
nbformat==5.2.0
nest-asyncio==1.5.4
notebook==6.4.10
packaging==21.3
pandocfilters==1.5.0
parso==0.8.3
pexpect==4.8.0
pg8000==1.24.1
pickleshare==0.7.5
prometheus-client==0.13.1
prompt-toolkit==3.0.28
psutil==5.9.0
ptyprocess==0.7.0
pure-eval==0.2.2
pycparser==2.21
Pygments==2.11.2
pyparsing==3.0.7
pyrsistent==0.18.1
python-dateutil==2.8.2
pyzmq==22.3.0
scramp==1.4.1
Send2Trash==1.8.0
six==1.16.0
soupsieve==2.3.1
SQLAlchemy==1.4.32
stack-data==0.2.0
terminado==0.13.3
testpath==0.6.0
tornado==6.1
traitlets==5.1.1
wcwidth==0.2.5
webencodings==0.5.1

SQLAlchemy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "55521b9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.sql import text\n",
    "# https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-textual-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "223d5c5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('postgresql+pg8000://target:target@localhost:5432/reports', echo=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "eff88bb6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-03-30 15:00:55,076 INFO sqlalchemy.engine.Engine select pg_catalog.version()\n",
      "2022-03-30 15:00:55,077 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2022-03-30 15:00:55,079 INFO sqlalchemy.engine.Engine select current_schema()\n",
      "2022-03-30 15:00:55,079 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2022-03-30 15:00:55,081 INFO sqlalchemy.engine.Engine show standard_conforming_strings\n",
      "2022-03-30 15:00:55,081 INFO sqlalchemy.engine.Engine [raw sql] ()\n"
     ]
    }
   ],
   "source": [
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cf861f8e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-03-30 15:00:55,091 INFO sqlalchemy.engine.Engine SELECT NOW() as now;\n",
      "SELECT NOW() as now2;\n",
      "SELECT NOW() as now3;\n",
      "2022-03-30 15:00:55,092 INFO sqlalchemy.engine.Engine [generated in 0.00079s] ()\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as con:\n",
    "    with open(\"./query.sql\") as file:\n",
    "        query = text(file.read())\n",
    "        result = con.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "66aa3a6c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(datetime.datetime(2022, 3, 30, 18, 0, 55, 92844, tzinfo=datetime.timezone.utc),),\n",
       " (datetime.datetime(2022, 3, 30, 18, 0, 55, 92844, tzinfo=datetime.timezone.utc),),\n",
       " (datetime.datetime(2022, 3, 30, 18, 0, 55, 92844, tzinfo=datetime.timezone.utc),)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80a725e5",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}