03-Defining Schema in SQLAlchemy Core.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "posts\n",
      "users\n",
      "-------------\n",
      "users\n",
      "posts\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, Column, Text, DateTime, Boolean, ForeignKey\n",
    "\n",
    "metadata = MetaData()\n",
    "\n",
    "user = Table('users', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('user', String(200), nullable=False),\n",
    ")\n",
    "\n",
    "posts = Table('posts', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('post_title', String(200), nullable=False),\n",
    "    Column('post_slug', String(200),  nullable=False),\n",
    "    Column('content', Text(),  nullable=False),\n",
    "    Column('user_id', Integer(), ForeignKey(\"users.id\")),\n",
    ")\n",
    "\n",
    "for t in metadata.tables:\n",
    "    print(metadata.tables[t])\n",
    "\n",
    "print('-------------')  \n",
    "\n",
    "for t in metadata.sorted_tables:\n",
    "    print(t.name) # print table name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['posts.id', 'posts.post_title', 'posts.post_slug', 'posts.content', 'posts.user_id']\n",
      "['posts.id', 'posts.post_title', 'posts.post_slug', 'posts.content', 'posts.user_id']\n",
      "{ForeignKey('users.id')}\n",
      "PrimaryKeyConstraint(Column('id', Integer(), table=<posts>, primary_key=True, nullable=False))\n",
      "MetaData(bind=None)\n",
      "post_title\n",
      "VARCHAR(200)\n"
     ]
    }
   ],
   "source": [
    "print(posts.columns)         # return a list of columns\n",
    "print(posts.c)               # same as posts.columns\n",
    "print(posts.foreign_keys)    # returns a set containing foreign keys on the table\n",
    "print(posts.primary_key)     # returns the primary key of the column\n",
    "print(posts.metadata)        # get the MetaData object from the table\n",
    "print(posts.columns.post_title.name)     # returns the name of the column\n",
    "print(posts.columns.post_title.type)     # returns the type of the column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/pp/project/sqlalchemy/env/lib/python3.5/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
      "  \"\"\")\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\n",
    "    Column, DateTime, ForeignKey, Numeric, CheckConstraint\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "metadata = MetaData()\n",
    "\n",
    "engine = create_engine(\"postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts\")\n",
    "\n",
    "customers = Table('customers', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('first_name', String(100), nullable=False),\n",
    "    Column('last_name', String(100), nullable=False),\n",
    "    Column('username', String(50), nullable=False),\n",
    "    Column('email', String(200), nullable=False),\n",
    "    Column('address', String(200), nullable=False),\n",
    "    Column('town', String(50), nullable=False),\n",
    "    Column('created_on', DateTime(), default=datetime.now),\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    ")\n",
    "\n",
    "\n",
    "items = Table('items', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('name', String(200), nullable=False),\n",
    "    Column('cost_price', Numeric(10, 2), nullable=False),\n",
    "    Column('selling_price', Numeric(10, 2),  nullable=False),\n",
    "    Column('quantity', Integer(), nullable=False),\n",
    "    CheckConstraint('quantity > 0', name='quantity_check')\n",
    ")\n",
    "\n",
    "\n",
    "orders = Table('orders', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('customer_id', ForeignKey('customers.id')),\n",
    "    Column('date_placed', DateTime(), default=datetime.now),\n",
    "    Column('date_shipped', DateTime())\n",
    ")\n",
    "\n",
    "\n",
    "order_lines = Table('order_lines', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('order_id', ForeignKey('orders.id')),\n",
    "    Column('item_id', ForeignKey('items.id')),\n",
    "    Column('quantity', Integer())\n",
    ")\n",
    "\n",
    "\n",
    "metadata.create_all(engine)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}