SQLAlchemyCore.ipynb

{
 "metadata": {
  "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.8.6-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Python version: 3.8.6\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "from datetime import datetime\n",
    "from sqlalchemy import (create_engine, MetaData, Column, Table, Integer, String, DateTime, select, update, delete)\n",
    "\n",
    "print(\"Python version:\", sys.version[:5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///db/sqlachemycore.db\",echo=True)\n",
    "\n",
    "metadata = MetaData(bind=engine)"
   ]
  },
  {
   "source": [
    "# DB Initialization\n",
    "## Defining a table"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_table = Table(\"users\", metadata,\n",
    "                   Column(\"id\", Integer, primary_key=True),\n",
    "                   Column(\"name\", String(40), index=True),\n",
    "                   Column(\"age\", Integer, nullable=False),\n",
    "                   Column(\"password\", String),\n",
    "                   Column(\"created\", DateTime, default=datetime.now),\n",
    "                   Column(\"last_change\", DateTime, \n",
    "                          default=datetime.now,\n",
    "                          on_update=datetime.now))"
   ]
  },
  {
   "source": [
    "## Creating tables"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2020-11-04 18:22:00,579 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-11-04 18:22:00,579 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-11-04 18:22:00,580 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-11-04 18:22:00,581 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "metadata.create_all()"
   ]
  },
  {
   "source": [
    "# Inserting\n"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2020-11-04 11:36:13,382 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, age, password, created, last_change) VALUES (?, ?, ?, ?, ?)\n",
      "2020-11-04 11:36:13,382 INFO sqlalchemy.engine.base.Engine ('Fábio', 29, 'riodejaneiro', '2020-11-04 11:36:13.382542', '2020-11-04 11:36:13.382542')\n",
      "2020-11-04 11:36:13,384 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1d2372937c0>"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "ins = user_table.insert()\n",
    "new_user = ins.values(name=\"Fábio\",\n",
    "                     age=29,\n",
    "                     password=\"riodejaneiro\")\n",
    "\n",
    "conn.execute(new_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2020-11-04 11:40:51,788 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, age, password, created, last_change) VALUES (?, ?, ?, ?, ?)\n",
      "2020-11-04 11:40:51,789 INFO sqlalchemy.engine.base.Engine (('Marivaldo', 35, 'gatinho_123', '2020-11-04 11:40:51.788687', '2020-11-04 11:40:51.788687'), ('Jean', 19, 'jeanzinho_123', '2020-11-04 11:40:51.788687', '2020-11-04 11:40:51.788687'), ('Juancy', 27, 'souzapereira', '2020-11-04 11:40:51.788687', '2020-11-04 11:40:51.788687'), ('Luciana', 25, 'gatinha_luluzinha', '2020-11-04 11:40:51.788687', '2020-11-04 11:40:51.788687'))\n",
      "2020-11-04 11:40:51,790 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1d251c751f0>"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "conn.execute(user_table.insert(), [\n",
    "    {\"name\": \"Marivaldo\", \"age\": 35, \"password\": \"gatinho_123\"},\n",
    "    {\"name\": \"Jean\", \"age\": 19, \"password\": \"jeanzinho_123\"},\n",
    "    {\"name\": \"Juancy\", \"age\": 27, \"password\": \"souzapereira\"},\n",
    "    {\"name\": \"Luciana\", \"age\": 25, \"password\": \"gatinha_luluzinha\"}\n",
    "])"
   ]
  },
  {
   "source": [
    "# Select"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2020-11-04 18:24:05,318 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.age, users.password, users.created, users.last_change \n",
      "FROM users\n",
      "2020-11-04 18:24:05,319 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "s = select([user_table])\n",
    "result = s.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(1, 'Fábio', 29, 'riodejaneiro', datetime.datetime(2020, 11, 4, 11, 36, 13, 382542), datetime.datetime(2020, 11, 4, 11, 36, 13, 382542))\n(2, 'Marivaldo', 35, 'gatinho_123', datetime.datetime(2020, 11, 4, 11, 40, 51, 788687), datetime.datetime(2020, 11, 4, 11, 40, 51, 788687))\n(3, 'Jean', 19, 'jeanzinho_123', datetime.datetime(2020, 11, 4, 11, 40, 51, 788687), datetime.datetime(2020, 11, 4, 11, 40, 51, 788687))\n(4, 'Juancy', 27, 'souzapereira', datetime.datetime(2020, 11, 4, 11, 40, 51, 788687), datetime.datetime(2020, 11, 4, 11, 40, 51, 788687))\n(5, 'Luciana', 25, 'gatinha_luluzinha', datetime.datetime(2020, 11, 4, 11, 40, 51, 788687), datetime.datetime(2020, 11, 4, 11, 40, 51, 788687))\n"
     ]
    }
   ],
   "source": [
    "for row in result:\n",
    "    print(row)"
   ]
  },
  {
   "source": [
    "# Update"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2020-11-04 18:30:13,528 INFO sqlalchemy.engine.base.Engine UPDATE users SET name=? WHERE users.name = ?\n",
      "2020-11-04 18:30:13,529 INFO sqlalchemy.engine.base.Engine ('Juacy', 'Juancy')\n",
      "2020-11-04 18:30:13,532 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "u = update(user_table).where(user_table.c.name == \"Juancy\")\n",
    "\n",
    "u = u.values(name=\"Juacy\")\n",
    "\n",
    "result = conn.execute(u)\n",
    "\n",
    "result.rowcount"
   ]
  },
  {
   "source": [
    "# Delete"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2020-11-04 18:43:43,599 INFO sqlalchemy.engine.base.Engine DELETE FROM users WHERE users.name = ?\n",
      "2020-11-04 18:43:43,601 INFO sqlalchemy.engine.base.Engine ('Juacy',)\n",
      "2020-11-04 18:43:43,603 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "source": [
    "d = delete(user_table).where(user_table.c.name == \"Juacy\")\n",
    "\n",
    "result = conn.execute(d)\n",
    "\n",
    "result.rowcount"
   ]
  }
 ]
}