45:["$","title",null,{"children":"Python Connectivity - Beyond raw SQL | CalPoly - Lucas Pierce | Observable"}] 4d:T416,# Parameter Binding I originally had you write statements in SQL Alchemy just using string concatenation. For instance, if you wanted to insert an event into an events table with a name passed in to a method, you would have written: ```python def create_event(name): with engine.begin() as conn: conn.execute( sqlalchemy.text(f"INSERT INTO events (name) VALUES ({name})")) ``` The problem with string concatenation of inputs like this is introduces the possibility of a SQL Injection attack when you concatenate un-sanitized input directly into your query string. We'll walk through more specifics of that in the future when we cover security. Instead, you should use a feature called parameter binding. Here is an example: ```python def create_event(name): with engine.begin() as conn: conn.execute( sqlalchemy.text("INSERT INTO events (name) VALUES (:name)"), [{"name": name}]) ``` This is the syntax in SQLAlchemy, but every modern database library has similar functionality when executing raw SQL queries.4e:T591,When you execute a SQL query, it returns back what is known as a Cursor. A database cursor allows you to navigate row-by-row through the records of a database. In SQLAlchemy, it calls this returned object a CursorResult. In the above example, we traverse the rows of the result row-by-row. Similarly, if you want to just grab the first row, I directed you to just call first(). There are several other, more convenient, and more resilient ways of accessing your results. First, if you want to just access a single value of a single row, you can use scalar_one(): ```python conn.execute(sqlalchemy.text("SELECT price FROM potions WHERE sku='RED'")).scalar_one() ``` This method is convenient because if the query returns more than one row, it will throw an exception rather than silently letting you potentially do the wrong thing. If you want to return a series of scalar values, you can call scalars() combined with all(). For example: ```python conn.execute(sqlalchemy.text("SELECT id FROM potions")).scalars().all() ``` You could also turn the entire CursorResult into an array of tuples by simply calling all(): ```python conn.execute(sqlalchemy.text("SELECT * FROM potions")).all() ``` I'm not going to exhaustively cover all the ways of parsing database tables and recommend you check out [SQLAlchemy's CursorResult reference](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.CursorResult). 4f:T4a0,# A more pythonic way to write sql Languages generally support everything from injecting raw sql to a full ORM (Object-Relational Mapping) that allows you to purely interact with the database through objects in your language. As an intermediate stage, many libraries offer an in-between state that turns SQL operators into functions. I think of this as a query-builder style library. Below is an example of doing so in SQLAlchemy with what they call their Core library. ```python # Use reflection to derive table schema. You can also code this in manually. metadata_obj = sqlalchemy.MetaData() events = sqlalchemy.Table("events", metadata_obj, autoload_with=engine) # Insert two events, one named something, and the other named else. with engine.begin() as conn: conn.execute( sqlalchemy.insert(events), [ {"name": "something"}, {"name": "else"}, ], ) ``` ```python with engine.connect() as conn: # Select for all events available result = conn.execute( sqlalchemy.select(events) ) for id, event_name, created_at in result: print(f"id: {id}, name: {event_name}, create_at: {created_at}") ```50:T6ec,## Using for optional parameters Writing in this more pythonic way is especially helpful when implementing optional parameters. For example, if we wanted to implement a search endpoint that had various optional parameters in SQL, we could write the following code: ```python if sort is movie_sort_options.movie_title: order_by = db.movies.c.title elif sort is movie_sort_options.year: order_by = db.movies.c.year elif sort is movie_sort_options.rating: order_by = sqlalchemy.desc(db.movies.c.imdb_rating) else: assert False stmt = ( sqlalchemy.select( db.movies.c.movie_id, db.movies.c.title, db.movies.c.year, db.movies.c.imdb_rating, db.movies.c.imdb_votes, ) .limit(limit) .offset(offset) .order_by(order_by, db.movies.c.movie_id) ) # filter only if name parameter is passed if name != "": stmt = stmt.where(db.movies.c.title.ilike(f"%{name}%")) with db.engine.connect() as conn: result = conn.execute(stmt) json = [] for row in result: json.append( { "movie_id": row.movie_id, "movie_title": row.title, "year": row.year, "imdb_rating": row.imdb_rating, "imdb_votes": row.imdb_votes, } ) return json ``` We will be doing something similar to this in the fifth version of our potion shops, so you can revisit this code at that time. The benefit here, is the code is more readable when trying to dynamically compose a query, vs trying to doing the equivalent with string concatenating strings of raw SQL.51:T66f,# Errors SQL statements can have various run-time errors that you need to be prepared for. All database errors get translated in SQLAlchemy to a DBAPIError. This error has many subtypes, for example integrity checks get sent over as IntegrityError. If an exception is raised within a given ```with engine.begin```, all statements inside the with will be rolled back, even if they already executed. This is because ```with engine.begin``` opens a transaction, and a transaction guarantees atomicity of all statements made within it. We will cover transactions in more detail later, but for now make sure you are grouping together all statements you want to execute together in the same ```with engine.begin```. As an example, in the code below, we first insert an event named grouchy. We then create an event named test2 but we manually set the primary key to 1. There already exists a primary key with the ID of 1. What changes do you think this code will make to the DB? Given the following entries in our database for events: ``` id: 1, name: test data 2, create_at: 2022-11-04 14:40:36 id: 39, name: test data, create_at: 2022-11-04 14:43:08 ``` What will the following python do? ```python try: with engine.begin() as conn: conn.execute(sqlalchemy.insert(events).values(name = "grouchy")) conn.execute(sqlalchemy.text("DELETE FROM events where id > 1")) # This will cause an error conn.execute( sqlalchemy.text("INSERT INTO events (id, name) VALUES (:x, :y)"), [{"x": 1, "y": "test data 2"}] ) except DBAPIError as error: print(f"Error returned: <<<{error}>>>") ```52:[] 49:{"id":"6b2d421a0d3ead05","slug":"python-connectivity","title":"Python Connectivity - Beyond raw SQL","thumbnail":"d5fab6af4fb4376ee31d9725512c311bac3c541615eaf24e1026faa90d2411ec","access_level":"unlisted","create_time":"$D2022-11-04T02:21:31.080Z","update_time":"$D2024-10-08T16:53:35.034Z","view_time":null,"first_public_time":"$D2022-11-05T01:09:42.790Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"6a5e20e4d64ef88f","login":"calpoly-pierce","name":"CalPoly - Lucas Pierce","avatar_url":"https://avatars-next.observableusercontent.com/cfaa5efaa33b18f2d5d8db6344505f87f8262010d1b723fe872c7aa896d9e10e","tier":"education_2026","bio":"","followed_by_you":false,"follows_you":false},"version":103,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"22a220f50e98c544","login":"lucaspierce","name":"Lucas Pierce","avatar_url":"https://avatars-next.observableusercontent.com/096ba5a9676838eae82dcfc6173577bb075b75c6061c4644636dc33195dcff7f","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"22a220f50e98c544","login":"lucaspierce","name":"Lucas Pierce","avatar_url":"https://avatars-next.observableusercontent.com/096ba5a9676838eae82dcfc6173577bb075b75c6061c4644636dc33195dcff7f","tier":null,"role":"editor"},{"type":"team","id":"6a5e20e4d64ef88f","login":"calpoly-pierce","name":"CalPoly - Lucas Pierce","avatar_url":"https://avatars-next.observableusercontent.com/cfaa5efaa33b18f2d5d8db6344505f87f8262010d1b723fe872c7aa896d9e10e","tier":"education_2026","role":"viewer"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":12,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Python Connectivity - Beyond raw SQL","cells":[{"id":0,"value":"# Python Connectivity - Beyond raw SQL\nLucas Pierce - CSC-365\n\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"$4d","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":86,"value":"## Reading results in SQL Alchemy","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":9,"value":"There are many convenience functions in SQLAlchemy for reading results from queries. The most basic is what I first taught you, which is just iterating over the results themselves:\n\n```python\nwith engine.begin() as conn:\n result = conn.execute(sqlalchemy.text(\"SELECT * FROM events\"))\n for id, event_name, created_at in result:\n print(f\"id: {id}, name: {event_name}, create_at: {created_at}\")\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":12,"value":"{\n const results = md`~~~\nid: 1, name: LOGIN, create_at: 2022-10-31 04:12:21\nid: 2, name: LOGOUT, create_at: 2022-10-31 04:12:21\nid: 3, name: LOGIN, create_at: 2022-10-31 04:12:41\nid: 4, name: LOGOUT, create_at: 2022-10-31 04:12:41\nid: 5, name: test, create_at: 2022-11-04 03:42:00\nid: 6, name: test2, create_at: 2022-11-04 03:42:00\nid: 7, name: test, create_at: 2022-11-04 03:42:24\nid: 8, name: test2, create_at: 2022-11-04 03:42:24\nid: 9, name: test, create_at: 2022-11-04 03:45:29\nid: 10, name: test2, create_at: 2022-11-04 03:45:29\nid: 11, name: test, create_at: 2022-11-04 03:48:00\nid: 12, name: test2, create_at: 2022-11-04 03:48:00\n\nProcess finished with exit code 0\n~~~`;\n \n const div = html`

`;\n div.append(results);\n return div;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":89,"value":"$4e","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":58,"value":"# Automatically deriving metadata\n\nA huge benefit of database schemas being explicitly defined is you can query those schema to automatically derive object models. What this means in SQLAlchemy's case, is you can automatically infer a more pythonic way of accessing your database directly from the schema itself. I have an example below. How this works will depend quite a bit on the specific library you are using though.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":60,"value":"```python\nmetadata_obj = sqlalchemy.MetaData()\nevents = sqlalchemy.Table(\"events\", metadata_obj, autoload_with=engine)\nfor col in events.c:\n print(f\"name: {col.name} type: {col.type}\")\n```\n\nwhich leads to the output of\n```\nname: id type: BIGINT\nname: created_at type: TIMESTAMP\nname: name type: TEXT\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":23,"value":"$4f","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":27,"value":"If I just want to select events where the name is \"test\"\n\n```python\nwith engine.connect() as conn:\n result = conn.execute(\n sqlalchemy.select(events).where(events.c.name == \"test\")\n )\n for id, event_name, created_at in result:\n print(f\"id: {id}, name: {event_name}, create_at: {created_at}\")\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":40,"value":"This language of python-ifying SQL goes deep and covers the breadth of SQL functionality that we've talked about in the past few weeks. I leave it to the student to read the [SQLAlchemy query builder reference](https://docs.sqlalchemy.org/en/20/core/expression_api.html).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":70,"value":"$50","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":29,"value":"$51","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":45,"value":"The result of this code is:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":48,"value":"{\n const results = md`~~~\nError returned: <<<(pymysql.err.IntegrityError) (1062, \"Duplicate entry '1' for key 'events.PRIMARY'\")\n[SQL: INSERT INTO events (id, name) VALUES (%(x)s, %(y)s)]\n[parameters: {'x': 1, 'y': 'test data 2'}]\n(Background on this error at: https://sqlalche.me/e/20/gkpj)>>>\nid: 1, name: test data 2, create_at: 2022-11-04 14:40:36\nid: 39, name: test data, create_at: 2022-11-04 14:43:08\n\nProcess finished with exit code 0\n~~~`;\n \n const div = html`

`;\n div.append(results);\n return div;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":54,"value":"You'll notice the insert of the grouchy event never occurred and the delete statement never executed. This is because all of these have been wrapped into a single unit known as a transaction that either succeeds or fails together.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":100,"value":"Here is a reference to [SQLAlchemy's exception documentation](https://docs.sqlalchemy.org/en/20/core/exceptions.html) if you want to learn more.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":72,"value":"# Object Relational Mappings (ORMs)\n\nYou'll notice if you look at the documentation of SQL Alchemy that it has a Core section and an ORM section. We've been using Core up until now. I am not going to go into detail on using ORMs. I'll ask for your initial v1 of your team project, that you use only the core library. But, if you so choose, you may convert your code to ORM in v2 and beyond of your team project.\n\nIn essence, ORMs attempts to abstract away interaction with the database and replace it with using objects. I am personally more anti-ORM since my experience is it leads to inefficient data access and the benefits tend to be more short-term/illusory. That said, SQLAlchemy's ORM implementation is pretty good from what I've seen and I would be tempted to use it in a real project to see how it works out in practice.\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":31,"value":"# Further reading\n[SQLAlchemy 2.0 Reference](https://docs.sqlalchemy.org/en/20/index.html)\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":15,"value":"# Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":17,"value":"","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"}],"files":"$W52","stdlib":"1"}} 44:null