Generate SQL for DB creation (SQLAlchemy)

readme.md

ORM->SQL

A small useful script that will generate an SQL script in the current working directory to setup the database

queries.sql

CREATE TABLE my_table (
    id SERIAL NOT NULL, 
    my_data VARCHAR, 
    PRIMARY KEY (id)
);

sqlalchemy_sql4db_gen.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, Integer, String\n",
    "from sqlalchemy import create_mock_engine, MetaData\n",
    "from sqlalchemy.orm import declarative_base\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "\n",
    "class MyOrmModel(Base):\n",
    "    __tablename__ = \"my_table\"\n",
    "\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)\n",
    "    my_data = Column(String)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "25c79cca",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-15T15:39:32.950481Z",
     "start_time": "2021-12-15T15:39:32.537458Z"
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import insert, update, delete\n",
    "from sqlalchemy.future import select\n",
    "\n",
    "\n",
    "def write_query(sql, *args, **kwargs) -> None:\n",
    "    \"\"\"Writes an sql command to a file\"\"\"\n",
    "    query = f\"{sql.compile(dialect=engine.dialect)}\"\n",
    "    query = query.replace(\"\\t\", \" \" * 4)\n",
    "    query = query.strip(\"\\n\").strip(\";\")\n",
    "    query = f\"{query};\\n\"\n",
    "    file.write(query)\n",
    "\n",
    "\n",
    "def actions_to_record() -> None:\n",
    "    \"\"\"Actions with the database that need to be recorded\"\"\"\n",
    "    # creating DB structure from ORM's models\n",
    "    metadata: MetaData = Base.metadata\n",
    "    metadata.create_all(engine, checkfirst=False)\n",
    "\n",
    "\n",
    "engine = create_mock_engine(\"postgresql://\", write_query)\n",
    "\n",
    "with open(\"queries.sql\", \"w\") as file:\n",
    "    actions_to_record()"
   ]
  }
 ],
 "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.7"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}