Putting SQL inside your code or The art of transferable skills aka - Party Like it’s ANSI 1999 aka - SQL is Back, this time it’s Relational aka - SQL is Coming Home 1 

Hi I’m Huss Co-founder & CTO @ 9fin 9fin.com @9finHQ 2 @HUSSTECH 

Audience Participation: Part 1 Who has done (literally) any SQL before? Who feels like their mastery of SQL is that of Michael Stonebraker himself? 3 

Audience Participation: Part 2 Who has done anything “data-sciencey” recently? Who has done that using Python as one of their tools? 4 

Audience Participation: Part 1 - Results! Who has done (literally) any SQL before? Who feels like their mastery of SQL is that of Michael Stonebraker himself? 5 Select * from I AM CODD 

Audience Participation: Part 2 - Results! Who has done anything “data-sciencey” recently? Who has done that using Python as one of their tools? 6 

SQL and Apps Today: Object Relational Mapping ● Model your entities using an ORM library for your application ○ SQLAlchemy (Python) ○ ActiveRecord (Rails) ● Takes care of data retrieval and persistence ● Great, all done...nothing more to do here? 7 Application runtime Models ORM Backend DB Driver 

SQL and Apps Today: Object Relational Mapping ● Great, all done...nothing more to do here? ○ No! ● There will be a point in your life, where you need to break out and write som raw SQL strings ○ Not long passes before we have a growing number of string literals in our code 8 Application runtime Models ORM Backend DB Driver 

SQLpy ● Write SQL *next* to your application code ● Prepares a ready to execute functools.partial object for you ● No query strings dotted all over source files ● A light wrapper around your DB Driver ● Genesis from YeSQL 9 In source control! queries.sql Application runtime Whatever you want DB Driver Source Control 

SQLpy + Composition 10 -- name: built_sql_query$ SELECT * FROM messages WHERE 1=1 AND id = %(id_low)s OR id = %(id_high)s AND message = %(msg)s; sql = sqlpy.Queries('queries.sql') .... args = {'id_low': 1} results = sql.BUILT_SQL_QUERY(cur, args) SELECT * FROM messages WHERE 1=1 AND id = 1; id message next_message 1 hello there 2 SQLpy NULL 3 databases rule! 4 hello friend Query still passes through the DB2 .0 API driver sanitiser for safe execution 

SQLpy: What, Why, How? ● This was my first time writing a large web application ○ Naive enough to (basically) ignore convention of an ORM ○ Just starting to get enough Python experience to be a danger to myself and others ○ I knew SQL, I liked SQL….I wanted SQL ○ ...so I went looking and found, YeSQL….and then started Pythoning ● I just made something which helped me at the start ○ As I found limitations and wanted it to do more, it developed further ● Using SQL directly is good in many ways ○ It is a high performance machine...why not use it? ○ Make use of advanced data structures ○ Do your computation where the data is ○ Enforce business rules 11 Thi t ra f le s il r 

Data Science: Python taking over! ● Multitude of tools have made Python the interface language of choice ● But the glue has been clunky bash scripts, python scripts, CSV dumps and loads ● Why not use use Python all the way through and really integrate your systems and pipelines with one universal language? 12 Database Object Store --> Log Streaming --> Distributed Computation 

Data Science: If you can’t beat em’ ● Next generation datastores are cloud native, distributed and big in scale ● Are converging on SQL as their data modelling language, BUT No traditional relational Database ORM support ● All provide Python libraries that expose their SQL language interfaces ○ kSQL (kafka), sparkSQL (Apache Spark), BigQuery (Google Cloud), Athena (AWS S3) 13 SQL SQL SQL SQL SQL 

Example: Google BigQuery API with SQL ● Since SQLpy has no opinion on the backend datastore interface mechanism, it just expects a DB 2.0 API compliant cursor object ○ Make an object defining execute, fetchone, fetchall etc… methods 14 https://github.com/HUSSTECH/talks-code/tree/master/party-like-its-ansi-1999 

sqlpy.readthedocs.io ● github.com/9fin/sqlpy (MIT) ● Made for PostgreSQL first ○ Other DBs working in theory, but not tested! ● Still

Thank You! References and Credits [slide#] [13] ksql: https://www.confluent.io/product/ksql/ [13] SparkSQL: https://spark.apache.org/sql/ [13] BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators [13] Athena: https://aws.amazon.com/athena/ [14] Google BigQuery sample applciation: https://cloud.google.com/bigquery/create-simple-app-api Emojis - https://clipart.info/emojipngclipart 16