{ "cells": [ { "cell_type": "markdown", "id": "3d9b7333", "metadata": {}, "source": [ "(sqlatutorial:working-with-transactions)=\n", "\n", "# Working with Transactions and the DBAPI\n", "\n", "With the {class}`~sqlalchemy.future.Engine` object ready to go, we may now proceed\n", "to dive into the basic operation of an {class}`~sqlalchemy.future.Engine` and\n", "its primary interactive endpoints, the {class}`~sqlalchemy.future.Connection` and\n", "{class}`~sqlalchemy.engine.Result`. We will additionally introduce the ORM's\n", "{term}`facade` for these objects, known as the {class}`~sqlalchemy.orm.Session`.\n", "\n", ":::{div} orm-header\n", "\n", "**Note to ORM readers**\n", "\n", "When using the ORM, the {class}`~sqlalchemy.future.Engine` is managed by another\n", "object called the {class}`~sqlalchemy.orm.Session`. The {class}`~sqlalchemy.orm.Session` in\n", "modern SQLAlchemy emphasizes a transactional and SQL execution pattern that\n", "is largely identical to that of the {class}`~sqlalchemy.future.Connection` discussed\n", "below, so while this subsection is Core-centric, all of the concepts here\n", "are essentially relevant to ORM use as well and is recommended for all ORM\n", "learners. The execution pattern used by the {class}`~sqlalchemy.future.Connection`\n", "will be contrasted with that of the {class}`~sqlalchemy.orm.Session` at the end\n", "of this section.\n", ":::\n", "\n", "As we have yet to introduce the SQLAlchemy Expression Language that is the\n", "primary feature of SQLAlchemy, we will make use of one simple construct within\n", "this package called the {func}`~sqlalchemy.sql.expression.text` construct, which allows us to write\n", "SQL statements as **textual SQL**.\n", "Rest assured that textual SQL in day-to-day SQLAlchemy use is by far the exception rather than the rule for most tasks, even though it always remains fully available." ] }, { "cell_type": "code", "execution_count": 1, "id": "37b953ce", "metadata": {}, "outputs": [], "source": [ "from sqlalchemy import create_engine\n", "engine = create_engine(\"sqlite+pysqlite:///:memory:\", echo=True, future=True)" ] }, { "cell_type": "markdown", "id": "30683b8c", "metadata": {}, "source": [ "\n", "\n", "(sqlatutorial:getting-connection)=\n", "\n", "## Getting a Connection\n", "\n", "The sole purpose of the {class}`~sqlalchemy.future.Engine` object from a user-facing\n", "perspective is to provide a unit of\n", "connectivity to the database called the {class}`~sqlalchemy.future.Connection`. When\n", "working with the Core directly, the {class}`~sqlalchemy.future.Connection` object\n", "is how all interaction with the database is done. As the {class}`~sqlalchemy.future.Connection`\n", "represents an open resource against the database, we want to always limit\n", "the scope of our use of this object to a specific context, and the best\n", "way to do that is by using Python context manager form, also known as\n", "[the with statement](https://docs.python.org/3/reference/compound_stmts.html#with).\n", "Below we illustrate \"Hello World\", using a textual SQL statement. Textual\n", "SQL is emitted using a construct called {func}`~sqlalchemy.sql.expression.text` that will be discussed\n", "in more detail later:" ] }, { "cell_type": "code", "execution_count": 2, "id": "f8a6cd8d", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,126 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,126 INFO sqlalchemy.engine.Engine select 'hello world'\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,127 INFO sqlalchemy.engine.Engine [generated in 0.00121s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "[('hello world',)]\n", "2021-10-04 01:45:44,128 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "from sqlalchemy import text\n", "\n", "with engine.connect() as conn:\n", " result = conn.execute(text(\"select 'hello world'\"))\n", " print(result.all())" ] }, { "cell_type": "markdown", "id": "3bebf8cd", "metadata": {}, "source": [ "In the above example, the context manager provided for a database connection\n", "and also framed the operation inside of a transaction. The default behavior of\n", "the Python DBAPI includes that a transaction is always in progress; when the\n", "scope of the connection is {term}`released`, a ROLLBACK is emitted to end the\n", "transaction. The transaction is **not committed automatically**; when we want\n", "to commit data we normally need to call {meth}`~sqlalchemy.future.Connection.commit`\n", "as we'll see in the next section.\n", "\n", ":::{tip}\n", "\"autocommit\" mode is available for special cases. The section\n", "{ref}`dbapi_autocommit` discusses this.\n", ":::\n", "\n", "The result of our SELECT was also returned in an object called\n", "{class}`~sqlalchemy.engine.Result` that will be discussed later, however for the moment\n", "we'll add that it's best to ensure this object is consumed within the\n", "\"connect\" block, and is not passed along outside of the scope of our connection.\n", "\n", "\n", "\n", "(sqlatutorial:committing-data)=\n", "\n", "## Committing Changes\n", "\n", "We just learned that the DBAPI connection is non-autocommitting. What if\n", "we want to commit some data? We can alter our above example to create a\n", "table and insert some data, and the transaction is then committed using\n", "the {meth}`~sqlalchemy.future.Connection.commit` method, invoked **inside** the block\n", "where we acquired the {class}`~sqlalchemy.future.Connection` object:" ] }, { "cell_type": "code", "execution_count": 3, "id": "0f41861e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,137 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,138 INFO sqlalchemy.engine.Engine CREATE TABLE some_table (x int, y int)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,138 INFO sqlalchemy.engine.Engine [generated in 0.00118s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,139 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,140 INFO sqlalchemy.engine.Engine [generated in 0.00048s] ((1, 1), (2, 4))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,141 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "# \"commit as you go\"\n", "with engine.connect() as conn:\n", " conn.execute(text(\"CREATE TABLE some_table (x int, y int)\"))\n", " conn.execute(\n", " text(\"INSERT INTO some_table (x, y) VALUES (:x, :y)\"),\n", " [{\"x\": 1, \"y\": 1}, {\"x\": 2, \"y\": 4}]\n", " )\n", " conn.commit()" ] }, { "cell_type": "markdown", "id": "73f2c915", "metadata": {}, "source": [ "Above, we emitted two SQL statements that are generally transactional, a\n", "\"CREATE TABLE\" statement [^id2] and an \"INSERT\" statement that's parameterized\n", "(the parameterization syntax above is discussed a few sections below in\n", "{ref}`sqlatutorial:multiple-parameters`). As we want the work we've done to be\n", "committed within our block, we invoke the\n", "{meth}`~sqlalchemy.future.Connection.commit` method which commits the transaction. After\n", "we call this method inside the block, we can continue to run more SQL\n", "statements and if we choose we may call {meth}`~sqlalchemy.future.Connection.commit`\n", "again for subsequent statements. SQLAlchemy refers to this style as **commit as\n", "you go**.\n", "\n", "There is also another style of committing data, which is that we can declare\n", "our \"connect\" block to be a transaction block up front. For this mode of\n", "operation, we use the {meth}`~sqlalchemy.future.Engine.begin` method to acquire the\n", "connection, rather than the {meth}`~sqlalchemy.future.Engine.connect` method. This method\n", "will both manage the scope of the {class}`~sqlalchemy.future.Connection` and also\n", "enclose everything inside of a transaction with COMMIT at the end, assuming\n", "a successful block, or ROLLBACK in case of exception raise. This style\n", "may be referred towards as **begin once**:" ] }, { "cell_type": "code", "execution_count": 4, "id": "983d642c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,150 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,151 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,152 INFO sqlalchemy.engine.Engine [cached since 0.01228s ago] ((6, 8), (9, 10))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,152 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "# \"begin once\"\n", "with engine.begin() as conn:\n", " conn.execute(\n", " text(\"INSERT INTO some_table (x, y) VALUES (:x, :y)\"),\n", " [{\"x\": 6, \"y\": 8}, {\"x\": 9, \"y\": 10}]\n", " )" ] }, { "cell_type": "markdown", "id": "4b79ed12", "metadata": {}, "source": [ "\"Begin once\" style is often preferred as it is more succinct and indicates the\n", "intention of the entire block up front. However, within this tutorial we will\n", "normally use \"commit as you go\" style as it is more flexible for demonstration\n", "purposes.\n", "\n", ":::{admonition} What's \"BEGIN (implicit)\"?\n", "You might have noticed the log line \"BEGIN (implicit)\" at the start of a\n", "transaction block. \"implicit\" here means that SQLAlchemy **did not\n", "actually send any command** to the database; it just considers this to be\n", "the start of the DBAPI's implicit transaction. You can register\n", "{ref}`event hooks ` to intercept this event, for example.\n", ":::\n", "\n", "[^id2]: {term}`DDL` refers to the subset of SQL that instructs the database\n", " to create, modify, or remove schema-level constructs such as tables. DDL\n", " such as \"CREATE TABLE\" is recommended to be within a transaction block that\n", " ends with COMMIT, as many databases uses transactional DDL such that the\n", " schema changes don't take place until the transaction is committed. However,\n", " as we'll see later, we usually let SQLAlchemy run DDL sequences for us as\n", " part of a higher level operation where we don't generally need to worry\n", " about the COMMIT.\n", "\n", "\n", "\n", "## Basics of Statement Execution\n", "\n", "We have seen a few examples that run SQL statements against a database, making\n", "use of a method called {meth}`~sqlalchemy.future.Connection.execute`, in conjunction with\n", "an object called {func}`~sqlalchemy.sql.expression.text`, and returning an object called\n", "{class}`~sqlalchemy.engine.Result`. In this section we'll illustrate more closely the\n", "mechanics and interactions of these components.\n", "\n", ":::{div} orm-header\n", "\n", "Most of the content in this section applies equally well to modern ORM\n", "use when using the {meth}`~sqlalchemy.orm.Session.execute` method, which works\n", "very similarly to that of {meth}`~sqlalchemy.future.Connection.execute`, including that\n", "ORM result rows are delivered using the same {class}`~sqlalchemy.engine.Result`\n", "interface used by Core.\n", ":::\n", "\n", "\n", "\n", "(sqlatutorial:fetching-rows)=\n", "\n", "### Fetching Rows\n", "\n", "We'll first illustrate the {class}`~sqlalchemy.engine.Result` object more closely by\n", "making use of the rows we've inserted previously, running a textual SELECT\n", "statement on the table we've created:" ] }, { "cell_type": "code", "execution_count": 5, "id": "5480e90c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,160 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,160 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,161 INFO sqlalchemy.engine.Engine [generated in 0.00102s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "x: 1 y: 1\n", "x: 2 y: 4\n", "x: 6 y: 8\n", "x: 9 y: 10\n", "2021-10-04 01:45:44,162 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "with engine.connect() as conn:\n", " result = conn.execute(text(\"SELECT x, y FROM some_table\"))\n", " for row in result:\n", " print(f\"x: {row.x} y: {row.y}\")" ] }, { "cell_type": "markdown", "id": "89e1100c", "metadata": {}, "source": [ "Above, the \"SELECT\" string we executed selected all rows from our table.\n", "The object returned is called {class}`~sqlalchemy.engine.Result` and represents an\n", "iterable object of result rows.\n", "\n", "{class}`~sqlalchemy.engine.Result` has lots of methods for\n", "fetching and transforming rows, such as the {meth}`~sqlalchemy.engine.Result.all`\n", "method illustrated previously, which returns a list of all {class}`~sqlalchemy.engine.Row`\n", "objects. It also implements the Python iterator interface so that we can\n", "iterate over the collection of {class}`~sqlalchemy.engine.Row` objects directly.\n", "\n", "The {class}`~sqlalchemy.engine.Row` objects themselves are intended to act like Python\n", "[named tuples](https://docs.python.org/3/library/collections.html#collections.namedtuple).\n", "Below we illustrate a variety of ways to access rows.\n", "\n", "- **Tuple Assignment** - This is the most Python-idiomatic style, which is to assign variables\n", " to each row positionally as they are received:\n", "\n", " ```\n", " result = conn.execute(text(\"select x, y from some_table\"))\n", "\n", " for x, y in result:\n", " # ...\n", " ```\n", "\n", "- **Integer Index** - Tuples are Python sequences, so regular integer access is available too:\n", "\n", " ```\n", " result = conn.execute(text(\"select x, y from some_table\"))\n", "\n", " for row in result:\n", " x = row[0]\n", " ```\n", "\n", "- **Attribute Name** - As these are Python named tuples, the tuples have dynamic attribute names\n", " matching the names of each column. These names are normally the names that the\n", " SQL statement assigns to the columns in each row. While they are usually\n", " fairly predictable and can also be controlled by labels, in less defined cases\n", " they may be subject to database-specific behaviors:\n", "\n", " ```\n", " result = conn.execute(text(\"select x, y from some_table\"))\n", "\n", " for row in result:\n", " y = row.y\n", "\n", " # illustrate use with Python f-strings\n", " print(f\"Row: {row.x} {row.y}\")\n", " ```\n", "\n", "- **Mapping Access** - To receive rows as Python **mapping** objects, which is\n", " essentially a read-only version of Python's interface to the common `dict`\n", " object, the {class}`~sqlalchemy.engine.Result` may be **transformed** into a\n", " {class}`~sqlalchemy.engine.MappingResult` object using the\n", " {meth}`~sqlalchemy.engine.Result.mappings` modifier; this is a result object that yields\n", " dictionary-like {class}`~sqlalchemy.engine.RowMapping` objects rather than\n", " {class}`~sqlalchemy.engine.Row` objects:\n", "\n", " ```\n", " result = conn.execute(text(\"select x, y from some_table\"))\n", "\n", " for dict_row in result.mappings():\n", " x = dict_row['x']\n", " y = dict_row['y']\n", " ```\n", "\n", "\n", "\n", "(sqlatutorial:sending-parameters)=\n", "\n", "### Sending Parameters\n", "\n", "SQL statements are usually accompanied by data that is to be passed with the\n", "statement itself, as we saw in the INSERT example previously. The\n", "{meth}`~sqlalchemy.future.Connection.execute` method therefore also accepts parameters,\n", "which are referred towards as {term}`bound parameters`. A rudimentary example\n", "might be if we wanted to limit our SELECT statement only to rows that meet a\n", "certain criteria, such as rows where the \"y\" value were greater than a certain\n", "value that is passed in to a function.\n", "\n", "In order to achieve this such that the SQL statement can remain fixed and\n", "that the driver can properly sanitize the value, we add a WHERE criteria to\n", "our statement that names a new parameter called \"y\"; the {func}`~sqlalchemy.sql.expression.text`\n", "construct accepts these using a colon format \"`:y`\". The actual value for\n", "\"`:y`\" is then passed as the second argument to\n", "{meth}`~sqlalchemy.future.Connection.execute` in the form of a dictionary:" ] }, { "cell_type": "code", "execution_count": 6, "id": "2ff38b0e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,170 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,171 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,171 INFO sqlalchemy.engine.Engine [generated in 0.00119s] (2,)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "x: 2 y: 4\n", "x: 6 y: 8\n", "x: 9 y: 10\n", "2021-10-04 01:45:44,173 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "with engine.connect() as conn:\n", " result = conn.execute(\n", " text(\"SELECT x, y FROM some_table WHERE y > :y\"),\n", " {\"y\": 2}\n", " )\n", " for row in result:\n", " print(f\"x: {row.x} y: {row.y}\")" ] }, { "cell_type": "markdown", "id": "392d8d0c", "metadata": {}, "source": [ "In the logged SQL output, we can see that the bound parameter `:y` was\n", "converted into a question mark when it was sent to the SQLite database.\n", "This is because the SQLite database driver uses a format called \"qmark parameter style\",\n", "which is one of six different formats allowed by the DBAPI specification.\n", "SQLAlchemy abstracts these formats into just one, which is the \"named\" format\n", "using a colon.\n", "\n", ":::{admonition} Always use bound parameters\n", "As mentioned at the beginning of this section, textual SQL is not the usual\n", "way we work with SQLAlchemy. However, when using textual SQL, a Python\n", "literal value, even non-strings like integers or dates, should **never be\n", "stringified into SQL string directly**; a parameter should **always** be\n", "used. This is most famously known as how to avoid SQL injection attacks\n", "when the data is untrusted. However it also allows the SQLAlchemy dialects\n", "and/or DBAPI to correctly handle the incoming input for the backend.\n", "Outside of plain textual SQL use cases, SQLAlchemy's Core Expression API\n", "otherwise ensures that Python literal values are passed as bound parameters\n", "where appropriate.\n", ":::\n", "\n", "(sqlatutorial:multiple-parameters)=\n", "\n", "### Sending Multiple Parameters\n", "\n", "In the example at {ref}`sqlatutorial:committing-data`, we executed an INSERT\n", "statement where it appeared that we were able to INSERT multiple rows into the\n", "database at once. For statements that **operate upon data, but do not return\n", "result sets**, namely {term}`DML` statements such as \"INSERT\" which don't\n", "include a phrase like \"RETURNING\", we can send **multi params** to the\n", "{meth}`~sqlalchemy.future.Connection.execute` method by passing a list of dictionaries\n", "instead of a single dictionary, thus allowing the single SQL statement to\n", "be invoked against each parameter set individually:" ] }, { "cell_type": "code", "execution_count": 7, "id": "6ff6aa88", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,181 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,182 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,182 INFO sqlalchemy.engine.Engine [cached since 0.04272s ago] ((11, 12), (13, 14))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,183 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "with engine.connect() as conn:\n", " conn.execute(\n", " text(\"INSERT INTO some_table (x, y) VALUES (:x, :y)\"),\n", " [{\"x\": 11, \"y\": 12}, {\"x\": 13, \"y\": 14}]\n", " )\n", " conn.commit()" ] }, { "cell_type": "markdown", "id": "35bafe58", "metadata": {}, "source": [ "Behind the scenes, the {class}`~sqlalchemy.future.Connection` objects uses a DBAPI feature\n", "known as [cursor.executemany()](https://www.python.org/dev/peps/pep-0249/#id18). This method performs the\n", "equivalent operation of invoking the given SQL statement against each parameter\n", "set individually. The DBAPI may optimize this operation in a variety of ways,\n", "by using prepared statements, or by concatenating the parameter sets into a\n", "single SQL statement in some cases. Some SQLAlchemy dialects may also use\n", "alternate APIs for this case, such as the {ref}`psycopg2 dialect for PostgreSQL ` which uses more performant APIs\n", "for this use case.\n", "\n", ":::{tip}\n", "you may have noticed this section isn't tagged as an ORM concept.\n", "That's because the \"multiple parameters\" use case is **usually** used\n", "for INSERT statements, which when using the ORM are invoked in a different\n", "way. Multiple parameters also may be used with UPDATE and DELETE\n", "statements to emit distinct UPDATE/DELETE operations on a per-row basis,\n", "however again when using the ORM, there is a different technique\n", "generally used for updating or deleting many individual rows separately.\n", ":::\n", "\n", "\n", "\n", "(sqlatutorial:bundling-parameters)=\n", "\n", "### Bundling Parameters with a Statement\n", "\n", "The two previous cases illustrate a series of parameters being passed to\n", "accompany a SQL statement. For single-parameter statement executions,\n", "SQLAlchemy's use of parameters is in fact more often than not done by\n", "**bundling** the parameters with the statement itself, which is a primary\n", "feature of the SQL Expression Language and makes for queries that can be\n", "composed naturally while still making use of parameterization in all cases.\n", "This concept will be discussed in much more detail in the sections that follow;\n", "for a brief preview, the {func}`~sqlalchemy.sql.expression.text` construct itself being part of the\n", "SQL Expression Language supports this feature by using the\n", "{meth}`~sqlalchemy.sql.expression.TextClause.bindparams` method; this is a {term}`generative` method that\n", "returns a new copy of the SQL construct with additional state added, in this\n", "case the parameter values we want to pass along:" ] }, { "cell_type": "code", "execution_count": 8, "id": "cd6bb423", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,191 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,192 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,193 INFO sqlalchemy.engine.Engine [generated in 0.00119s] (6,)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "x: 6 y: 8\n", "x: 9 y: 10\n", "x: 11 y: 12\n", "x: 13 y: 14\n", "2021-10-04 01:45:44,194 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "stmt = text(\"SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y\").bindparams(y=6)\n", "with engine.connect() as conn:\n", " result = conn.execute(stmt)\n", " for row in result:\n", " print(f\"x: {row.x} y: {row.y}\")" ] }, { "cell_type": "markdown", "id": "aff13f36", "metadata": {}, "source": [ "The interesting thing to note above is that even though we passed only a single\n", "argument, `stmt`, to the {meth}`~sqlalchemy.future.Connection.execute` method, the\n", "execution of the statement illustrated both the SQL string as well as the\n", "separate parameter tuple.\n", "\n", "\n", "\n", "(sqlatutorial:executing-orm-session)=\n", "\n", "## Executing with an ORM Session\n", "\n", "As mentioned previously, most of the patterns and examples above apply to\n", "use with the ORM as well, so here we will introduce this usage so that\n", "as the tutorial proceeds, we will be able to illustrate each pattern in\n", "terms of Core and ORM use together.\n", "\n", "The fundamental transactional / database interactive object when using the\n", "ORM is called the {class}`~sqlalchemy.orm.Session`. In modern SQLAlchemy, this object\n", "is used in a manner very similar to that of the {class}`~sqlalchemy.future.Connection`,\n", "and in fact as the {class}`~sqlalchemy.orm.Session` is used, it refers to a\n", "{class}`~sqlalchemy.future.Connection` internally which it uses to emit SQL.\n", "\n", "When the {class}`~sqlalchemy.orm.Session` is used with non-ORM constructs, it\n", "passes through the SQL statements we give it and does not generally do things\n", "much differently from how the {class}`~sqlalchemy.future.Connection` does directly, so\n", "we can illustrate it here in terms of the simple textual SQL\n", "operations we've already learned.\n", "\n", "The {class}`~sqlalchemy.orm.Session` has a few different creational patterns, but\n", "here we will illustrate the most basic one that tracks exactly with how\n", "the {class}`~sqlalchemy.future.Connection` is used which is to construct it within\n", "a context manager:" ] }, { "cell_type": "code", "execution_count": 9, "id": "7ec161d5", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,246 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,247 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,247 INFO sqlalchemy.engine.Engine [cached since 0.056s ago] (6,)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "x: 6 y: 8\n", "x: 9 y: 10\n", "x: 11 y: 12\n", "x: 13 y: 14\n", "2021-10-04 01:45:44,249 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "from sqlalchemy.orm import Session\n", "\n", "stmt = text(\"SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y\").bindparams(y=6)\n", "with Session(engine) as session:\n", " result = session.execute(stmt)\n", " for row in result:\n", " print(f\"x: {row.x} y: {row.y}\")" ] }, { "cell_type": "markdown", "id": "8354f788", "metadata": {}, "source": [ "The example above can be compared to the example in the preceding section\n", "in {ref}`sqlatutorial:bundling-parameters` - we directly replace the call to\n", "`with engine.connect() as conn` with `with Session(engine) as session`,\n", "and then make use of the {meth}`~sqlalchemy.orm.Session.execute` method just like we\n", "do with the {meth}`~sqlalchemy.future.Connection.execute` method.\n", "\n", "Also, like the {class}`~sqlalchemy.future.Connection`, the {class}`~sqlalchemy.orm.Session` features\n", "\"commit as you go\" behavior using the {meth}`~sqlalchemy.orm.Session.commit` method,\n", "illustrated below using a textual UPDATE statement to alter some of\n", "our data:" ] }, { "cell_type": "code", "execution_count": 10, "id": "60b62696", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,257 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,258 INFO sqlalchemy.engine.Engine UPDATE some_table SET y=? WHERE x=?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,258 INFO sqlalchemy.engine.Engine [generated in 0.00065s] ((11, 9), (15, 13))\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2021-10-04 01:45:44,259 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ "with Session(engine) as session:\n", " result = session.execute(\n", " text(\"UPDATE some_table SET y=:y WHERE x=:x\"),\n", " [{\"x\": 9, \"y\":11}, {\"x\": 13, \"y\": 15}]\n", " )\n", " session.commit()" ] }, { "cell_type": "markdown", "id": "71fc43fe", "metadata": {}, "source": [ "Above, we invoked an UPDATE statement using the bound-parameter, \"executemany\"\n", "style of execution introduced at {ref}`sqlatutorial:multiple-parameters`, ending\n", "the block with a \"commit as you go\" commit.\n", "\n", ":::{tip}\n", "The {class}`~sqlalchemy.orm.Session` doesn't actually hold onto the\n", "{class}`~sqlalchemy.future.Connection` object after it ends the transaction. It\n", "gets a new {class}`~sqlalchemy.future.Connection` from the {class}`~sqlalchemy.future.Engine`\n", "when executing SQL against the database is next needed.\n", ":::\n", "\n", "The {class}`~sqlalchemy.orm.Session` obviously has a lot more tricks up its sleeve\n", "than that, however understanding that it has a {meth}`~sqlalchemy.orm.Session.execute`\n", "method that's used the same way as {meth}`~sqlalchemy.future.Connection.execute` will\n", "get us started with the examples that follow later." ] } ], "metadata": { "jupytext": { "text_representation": { "extension": ".md", "format_name": "myst" } }, "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.8.6" }, "source_map": [ 16, 49, 52, 75, 81, 115, 124, 146, 153, 210, 215, 305, 313, 348, 355, 397, 403, 440, 448, 461, 468 ] }, "nbformat": 4, "nbformat_minor": 5 }