Last active
September 24, 2021 17:35
-
-
Save ryansb/3ad9b7ccf225d46a16dc to your computer and use it in GitHub Desktop.
Revisions
-
ryansb revised this gist
Oct 19, 2015 . 1 changed file with 3 additions and 3 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,9 +1,9 @@ Before running this notebook, run: ``` pip3 install jupyter SQLAlchemy psycopg2 ``` This will install the notebook server and database drivers needed to run these examples. For more information on installing Jupyter (formerly IPython notebook) see their [install guide](http://jupyter.readthedocs.org/en/latest/install.html). Once you've installed the dependencies, run `jupyter notebook` and it will open your web browser to the notebook's main page. Then upload this notebook (SQLTest.ipynb) and run it. -
Oct 19, 2015 . No changes.There are no files selected for viewing
-
Oct 19, 2015 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -4,6 +4,6 @@ Before running this notebook, run: pip install 'IPython[notebook]' SQLAlchemy psycopg2 ``` This will install the notebook server and database drivers needed to run these examples. For more information on installing IPython notebook (now called Jupyter) see their [install guide](http://jupyter.readthedocs.org/en/latest/install.html). Once you've installed the dependencies, run `ipython notebook` and it will open your web browser to the notebook's main page. Then upload this notebook (SQLTest.ipynb) and run it. -
Oct 19, 2015 . 1 changed file with 9 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,9 @@ Before running this notebook, run: ``` pip install 'IPython[notebook]' SQLAlchemy psycopg2 ``` This will install the notebook server and database drivers needed to run these examples. Once you've installed the dependencies, run `ipython notebook` and it will open your web browser to the notebook's main page. Then upload this notebook (SQLTest.ipynb) and run it. -
Oct 16, 2015 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,379 @@ { "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": true }, "outputs": [], "source": [ "connection_string = 'postgresql:///example'" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import json\n", "import sqlalchemy\n", "\n", "db = sqlalchemy.create_engine(connection_string)\n", "engine = db.connect()\n", "meta = sqlalchemy.MetaData(engine)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from sqlalchemy import Column, Integer, Text\n", "from sqlalchemy.dialects.postgresql import JSON, JSONB\n", "\n", "sqlalchemy.Table(\"jsontable\", meta,\n", " Column('id', Integer, primary_key=True),\n", " Column('name', Text),\n", " Column('email', Text),\n", " Column('doc', JSON))\n", "meta.create_all()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "<sqlalchemy.engine.result.ResultProxy at 0x7f786cca4e48>" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "j_table = sqlalchemy.table(\"jsontable\",\n", " Column('id', Integer),\n", " Column('name', Text),\n", " Column('email', Text),\n", " Column('doc', JSON))\n", "# drop existing records\n", "engine.execute(j_table.delete())" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "<sqlalchemy.engine.result.ResultProxy at 0x7f787d002d30>" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "engine.execute(\"\"\"INSERT INTO jsontable VALUES (1, 'Mr. RawSQL', '[email protected]', '{\"dialect\": \"raw\"}')\"\"\")" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "<sqlalchemy.engine.result.ResultProxy at 0x7f788399d6a0>" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"\"\"INSERT INTO jsontable VALUES (%(id)s, %(name)s, %(email)s, %(doc)s)\"\"\"\n", "engine.execute(query, id=2, name='Mr. Interpolated Query', email='[email protected]', doc=json.dumps({'dialect': 'string interpolation'}))" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [], "source": [ "s = j_table.select().where(j_table.c.name == \"Mr. RawSQL\")" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "(1, 'Mr. RawSQL', '[email protected]', {'dialect': 'raw'})" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "res = engine.execute(s)\n", "res.fetchone()" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "'INSERT INTO jsontable (id, name, doc) VALUES (:id, :name, :doc)'" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "str(j_table.insert().values(id=3, name=\"Mr. Params\", doc={\"hi\": \"There\"}))" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "INSERT INTO jsontable (id, name, email, doc) VALUES (:id, :name, :email, :doc)\n" ] } ], "source": [ "ins = j_table.insert().values(id=3,\n", " name=\"Mr. Params\",\n", " email=\"[email protected]\",\n", " doc={\"dialect\": \"params\",\n", " \"address\": {\"street\": \"Main St.\", \"zip\": 12345},\n", " },\n", " )\n", "print(str(ins))" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "<sqlalchemy.engine.result.ResultProxy at 0x7f788399d780>" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "engine.execute(ins)" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "(3, 'Mr. Params', '[email protected]', {'dialect': 'params', 'address': {'street': 'Main St.', 'zip': 12345}})" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "engine.execute(j_table.select().where(j_table.c.doc['dialect'].astext == 'params')).fetchone()" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "(3, 'Mr. Params', '[email protected]', {'dialect': 'params', 'address': {'street': 'Main St.', 'zip': 12345}})" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "engine.execute(j_table.select().where(j_table.c.doc[('address', 'zip')].cast(sqlalchemy.Integer) == 12345)).fetchone()" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from sqlalchemy.ext.declarative import declarative_base\n", "from sqlalchemy.orm import sessionmaker\n", "\n", "Base = declarative_base()\n", "class User(Base):\n", " __tablename__ = 'jsontable'\n", " id = Column(Integer, primary_key=True)\n", " name = Column(Text)\n", " email = Column(Text)\n", " doc = Column(JSON)\n", "\n", "Base.metadata.create_all(engine)\n", "SessionFactory = sessionmaker(engine)" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "collapsed": false }, "outputs": [], "source": [ "session = SessionFactory()\n", "u = User(\n", " id=4,\n", " name=\"Oscar ORM\",\n", " email=\"[email protected]\",\n", " doc={\"address\": {\"zip\": 5678, \"street\": \"Cross St.\"}})\n", "session.add(u)\n", "session.commit()" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Mr. Params\n", "{'dialect': 'params', 'address': {'street': 'Main St.', 'zip': 12345}}\n" ] } ], "source": [ "uu = session.query(User).filter(User.id == 3).one()\n", "print(uu.name)\n", "print(uu.doc)" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{'address': {'street': 'Cross St.', 'zip': 5678}}\n" ] } ], "source": [ "uu = session.query(User).filter(\n", " User.doc[\n", " ('address', 'zip')\n", " ].cast(sqlalchemy.Integer) == 5678\n", ").one()\n", "print(uu.doc)" ] } ], "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.0" } }, "nbformat": 4, "nbformat_minor": 0 }