{"id":270,"date":"2018-04-21T14:36:27","date_gmt":"2018-04-21T21:36:27","guid":{"rendered":"http:\/\/aklaver.org\/wordpress\/?p=270"},"modified":"2018-04-21T14:36:27","modified_gmt":"2018-04-21T21:36:27","slug":"building-dynamic-sql-using-psycopg2","status":"publish","type":"post","link":"https:\/\/aklaver.org\/wordpress\/2018\/04\/21\/building-dynamic-sql-using-psycopg2\/","title":{"rendered":"Building dynamic SQL using psycopg2"},"content":{"rendered":"

It has been awhile since I posted anything. Thought I would start back with a software
\npackage I use in many different situations, psycopg2<\/a> . Psycopg2 is a Python database adapter for Postgres that follows the Python DB API. It is feature rich and today I will introduce a feature new to the latest major release(2.7), namely the sql module for building complete SQL statements dynamically. I will give a quick run down of the basics below. For the official docs on the module see:
\nhttp:\/\/initd.org\/psycopg\/docs\/sql.html#module-psycopg2.sql<\/a>.
\nFor example data I will be using the Pagila database (derived from the MySQL
\nSakila sample db) obtained from here<\/a>.<\/p>\n

Setting up imports:<\/p>\n

\r\nfrom psycopg2 import connect, sql\r\nfrom psycopg2.extras import RealDictCursor\r\n<\/pre>\n
Create connection and cursor:<\/p>\n
\r\ncon = connect("dbname=pagila host=localhost user=postgres", \r\n              cursor_factory=RealDictCursor)\r\ncur = con.cursor()\r\n<\/pre>\n
Build simple query string:<\/p>\n
\r\nqry_str = sql.SQL("SELECT {}, {} FROM {}").format(\r\nsql.Identifier('customer_id'),\r\nsql.Identifier("first_name"),\r\nsql.Identifier("customer")\r\n)\r\nprint(qry_str.as_string(con))\r\nselect "customer_id", "first_name" from "customer"\r\ncur.execute(qry_str)\r\nrs = cur.fetchone()\r\nrs\r\n{'customer_id': 1, 'first_name': 'MARY'}\r\n<\/pre>\n
Breaking the above down. SQL is a class used to build the string and as such has
\na format method that follows that for the Python str.format() method. See psycopg2
\ndocs above for exceptions. Identifier is a class that handles those strings to be used
\nas query identifiers e.g. column\/field names versus data values. Therefore in the
\nabove the ‘{}’ are replaced in order by the Identifier values in the format().
\nTo verify the string you can use its as_string method provided you supply a connection
\nor cursor object to it. The query string is used in cur.execute() to fetch records
\nfrom the database. You can also build the query string directly in the execute()
\nto save a step.<\/p>\n
More compact\/flexible string construction:<\/p>\n
\r\nqry_str = sql.SQL("SELECT {} FROM {}").format(\r\nsql.SQL(",").join([sql.Identifier('customer_id'), \r\n                   sql.Identifier("first_name")]), \r\nsql.Identifier("customer")\r\n)\r\n<\/pre>\n
The above eliminates the need to provide a ‘{}’ for each field in the SELECT
\nfield list. Instead the .join() method to SQL is used build a comma separated
\nlist of field names. Helpful if you may be receiving a variable number of field names
\nfor different iterations of the query. As example where the contents of fld_list
\nmay change:<\/p>\n
\r\nfld_list = ["customer_id", "first_name", "last_name", "email"]\r\nqry_str = sql.SQL("SELECT {} FROM {}").format(\r\nsql.SQL(",").join(map(sql.Identifier, fld_list)),\r\nsql.Identifier("customer")\r\n)\r\nprint(qry_str.as_string(con))\r\nselect \r\n"customer_id","first_name","last_name","email" \r\nfrom "customer"\r\n<\/pre>\n
Generating placeholders in the query. The sql module supports two types of placeholders,
\n%s and %(name)s. For %s placeholders a sequence of values need to be provided
\ne.g. a list or tuple. For named placeholders a dictionary is used to supply values.<\/p>\n
\r\nplaceholder_str = sql.SQL("SELECT {} FROM {} WHERE first_name = {}").format(\r\nsql.SQL(",").join(map(sql.Identifier, fld_list)),\r\nsql.Identifier("customer"),\r\nsql.Placeholder()\r\n)\r\nprint(placeholder_str.as_string(con))\r\nselect \r\n"customer_id","first_name","last_name","email" \r\nfrom "customer" where first_name = %s\r\ncur.execute(placeholder_str, ["MARY"])\r\nrs = cur.fetchone()\r\nrs\r\n{'customer_id': 1,\r\n'email': 'MARY.SMITH@sakilacustomer.org',\r\n'first_name': 'MARY',\r\n'last_name': 'SMITH'}\r\n<\/pre>\n
In the above %s is created as the placeholder for the first_name field value in the
\nWHERE clause using the Placeholder class. To create a named placeholder the procedure is:<\/p>\n
\r\nplaceholder_str = sql.SQL("SELECT {} FROM {} WHERE first_name = {}").format(\r\nsql.SQL(",").join(map(sql.Identifier, fld_list)),\r\nsql.Identifier("customer"),\r\nsql.Placeholder(name='first_name')\r\n)\r\nprint(placeholder_str.as_string(con))\r\nselect \r\n"customer_id","first_name","last_name","email" \r\nfrom "customer" where first_name = %(first_name)s\r\ncur.execute(placeholder_str, {"first_name": "MARY"})\r\n<\/pre>\n
Where this leads to is a building a query string from data sourced from a Python dictionary:<\/p>\n
\r\nnew_customer_list = [\r\n{"store_id": 1, "first_name": "ADRIAN" , "last_name": "KLAVER", \r\n"email": "ADRIAN.KLAVER@sakilacustomer.org", "address_id": 67},\r\n{"store_id": 2, "first_name": "JANE" , "last_name": "DOE", \r\n"email": "JANE.DOE@sakilacustomer.org", "address_id": 7},\r\n{"store_id": 1, "first_name": "WHO" , "last_name": "KNOWS", \r\n"email": "WHO.KNOWS@sakilacustomer.org", "address_id": 189 }\r\n]\r\n\r\ninsert_flds = [fld_name for fld_name in new_customer_list[0].keys()]\r\n\r\ninsert_str = sql.SQL("INSERT INTO customer ({}) VALUES ({})").format(\r\nsql.SQL(",").join(map(sql.Identifier, insert_flds)),\r\nsql.SQL(",").join(map(sql.Placeholder, insert_flds))\r\n)\r\n\r\nfrom psycopg2.extras import execute_batch\r\n\r\nexecute_batch(cur, insert_str, new_customer_list)\r\n<\/pre>\n
I snuck in another new feature from psycopg2 2.7, execute_batch. This is way of batching statements for fewer round trips to the server, by default 100. For this example not really a win, but it is nice to know it is out there. For cases where I have a lot of data to transfer to a table I use psycopg2’s COPY features whenever possible. That is a subject for another day though.<\/p>\n","protected":false},"excerpt":{"rendered":"
It has been awhile since I posted anything. Thought I would start back with a software package I use in many different situations, psycopg2 . Psycopg2 is a Python database adapter for Postgres that follows the Python DB API. It … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-270","post","type-post","status-publish","format-standard","hentry","category-postgres"],"_links":{"self":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/270","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/comments?post=270"}],"version-history":[{"count":26,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/270\/revisions"}],"predecessor-version":[{"id":308,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/270\/revisions\/308"}],"wp:attachment":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/media?parent=270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/categories?post=270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/tags?post=270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}