{"id":172926,"date":"2023-11-05T11:23:02","date_gmt":"2023-11-05T10:23:02","guid":{"rendered":"https:\/\/liora.io\/en\/?p=172926"},"modified":"2026-02-06T08:50:33","modified_gmt":"2026-02-06T07:50:33","slug":"api-integration-in-python-databases-made-easy","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/api-integration-in-python-databases-made-easy","title":{"rendered":"API Integration in Python: Databases Made Easy"},"content":{"rendered":"<p>In our first article, we looked at some of the principles involved in building REST <a href=\"https:\/\/liora.io\/en\/api-the-path-to-seamless-integration\">APIs<\/a>, as well as a first example of their implementation in Python using Flask.<br \/>In this example, data was integrated into the code in the form of a list of dictionaries.<br \/>We also have an article on how to program and document an <strong>API with python, flask, swagger and connection.<\/strong><br \/>In the following example, we&#8217;ll look at how to connect an API to an <a href=\"https:\/\/liora.io\/en\/all-about-non-relational-databases\">external relational database<\/a> and allow items to be filtered by various conditions:<\/p>\n<ol>\n<li><a href=\"\/#API1\">Relational databases<\/a><\/li>\n<li><a href=\"\/#API2\">SQLite<\/a><\/li>\n<li><a href=\"\/#API3\">Connecting an API to the Chinook database<\/a><\/li>\n<li><a href=\"\/#API4\">Understanding the new API<\/a><\/li>\n<li><a href=\"https:\/\/liora.io\/programmer-et-documenter-une-api-avec-python-flask-swagger-et-connexion\">Using the new API<\/a><\/li>\n<li><a href=\"\/#API6\">Bibliographical references<\/a><\/li>\n<\/ol>\n<style>\nbody.elementor-page .elementor-widget-menu-anchor{margin-bottom:0}<\/style>\n<style>\n.elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading-title[class*=elementor-size-]>a{color:inherit;font-size:inherit;line-height:inherit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading .elementor-heading-title.elementor-size-xxl{font-size:59px}<\/style>\n<h3>1. Relational databases<\/h3>\n<p>Relational databases store and retrieve data in the form of tables.<\/p>\n<p>Tables are <strong>similar to spreadsheets:<\/strong> they <a href=\"https:\/\/liora.io\/en\/how-do-i-insert-columns-and-rows-in-excel\">have rows and columns,<\/a> with the columns indicating what the data corresponds to, such as a title or a date. The rows represent individual data items, which may correspond to users, transactions or any other type of entity.<\/p>\n<h3>2. SQLite<\/h3>\n<p>The database engine used in the suite is SQLite, a very lightweight database engine available under Python by default.<\/p>\n<p>The standard extension for <strong>SQLite files is .db.<\/strong><\/p>\n<p>The database used is the Chinook database (available via the following link), comprising 11 tables. In the following, we&#8217;ll be focusing on the employees table: this contains data on employees of the Chinook company, such as their ID, surname, first name, etc.<\/p>\n<h3>3. Connecting an API to the Chinook database<\/h3>\n<p>We start by copying the chinook.db database into our api directory.<\/p>\n<p>Our <strong>API will query this database<\/strong> to return the desired results to users.<\/p>\n<p>The corresponding code is as follows:<\/p>\n<p>We save it in the api directory under the name api4.py.<\/p>\n<p>To run it, launch a command line window from the api directory and <strong>enter the following commands:<\/strong><\/p>\n<p><strong>$ export FLASK_APP = api4.py<\/strong><\/p>\n<p><strong>$ export FLASK_ENV = development<\/strong><\/p>\n<p><strong>$ flask run<\/strong><\/p>\n<p>(to stop execution, press Ctrl-C).<\/p>\n<p>Among other messages, you get<\/p>\n<p>* Running on http:\/\/127.0.0.1:5000\/ (Press CTRL+C to quit)<\/p>\n<p>Once the program has run, you can submit requests like :<\/p>\n<p>http:\/\/127.0.0.1:5000\/api\/v1\/resources\/employees\/all<\/p>\n<p>http:\/\/127.0.0.1:5000\/api\/v1\/resources\/employees?LastName=Edwards<\/p>\n<p>http:\/\/127.0.0.1:5000\/api\/v1\/resources\/employees?LastName=Edwards&#038;City=Calgary<\/p>\n<p>http:\/\/127.0.0.1:5000\/api\/v1\/resources\/employees?City=Calgary<\/p>\n<p>The employees table in the Chinook database has 8 records, one for each Chinook employee.<\/p>\n<p>The data includes, among other things, the employee&#8217;s ID, surname, first name, city of residence, date of birth and date of recruitment.<\/p>\n<p>Our API allows filtering by three fields: EmployeeId, LastName and City.<\/p>\n<p>The new API responds to user requests by extracting information from the database using SQL queries.<\/p>\n<p>It also enables filtering by more than one field.<\/p>\n<h3>4. Understanding the new API<\/h3>\n<p>The employees table in the Chinook database is made up of eight columns: EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate and Address.<\/p>\n<p>Each row represents a Chinook employee.<\/p>\n<p>Rather than specifying the data in the code, our api_all function <a href=\"https:\/\/liora.io\/en\/database-what-is-it\">extracts it from the Chinook database:<\/a><\/p>\n<p>We start by <strong>connecting to the database<\/strong> using the sqlite3 library.<\/p>\n<p>An object representing the database connection is bound to the conn variable.<\/p>\n<p>The instruction conn.row_factory = dict_factory tells the object corresponding to the connection to use the dict_factory function, which returns results as dictionaries rather than lists &#8211; which converts better to JSON format.<\/p>\n<p>We then create a cursor object (cur = conn.cursor( )), which scans the database to extract the data.<\/p>\n<p>Finally, we execute a <a href=\"https:\/\/liora.io\/en\/like-sql-how-to-use-this-search-function\">SQL query<\/a> using the cur.execute method to extract all available data ( * ) from the employees table in our database.<\/p>\n<p>At the end of our function, the retrieved data is converted to JSON format: jsonify(all_employees).<\/p>\n<p>The other function returning data, api_filter, uses the same approach to extract specific data from the database.<\/p>\n<p>The purpose of the page_not_found function is to create an error page displayed to the user if he specifies a route that is not supported by the API:<\/p>\n<p>In HTML results, code 200 means &#8220;OK&#8221; (data transferred), while code 404 means &#8220;not found&#8221; (no resources available at the specified address).<\/p>\n<p>The page_not_found function returns 404 if something goes wrong.<\/p>\n<p>The api_filter function filters according to three fields: EmployeeId, LastName and City.<\/p>\n<p>It begins by identifying all the query parameters supplied in the URL, using the instruction :<\/p>\n<p>query_parameters = request.args<\/p>\n<p>It then retrieves the parameter values and binds them to variables:<\/p>\n<p>employeeid = query_parameters.get(&#8216;EmployeeId&#8217;)<\/p>\n<p>lastname = query_parameters.get(&#8216;LastName&#8217;)<\/p>\n<p>city = query_parameters.get(&#8216;City&#8217;)<\/p>\n<p>The following code portion is used to build an SQL query which is used to extract the required information from the database.<\/p>\n<p>The simplest SQL queries are of the form :<\/p>\n<p>SELECT &lt;columns&gt; FROM &lt;table&gt; WHERE &lt;column=match&gt; AND &lt;column=match&gt;;<\/p>\n<p>To obtain the data you&#8217;re looking for, you need to build both an SQL query of the type described above and a list with the specified filters (values).<\/p>\n<p>We begin by defining the query and the list of filters:<\/p>\n<p>Then, if EmployeeId, LastName and City have been passed as query parameters, they are added to the query and to the filter list:<\/p>\n<p>If the user has not specified any of these query parameters, the 404 error page is returned:<\/p>\n<p>To perfect our SQL query, we delete the last AND and complete the query with the semicolon required by SQL :<\/p>\n<p>Next, we connect to the database, then execute the SQL query built using our list of filters:<\/p>\n<p>Finally, we return the results in JSON format to the user:<\/p>\n<p>return jsonify(results)<\/p>\n<h3>5. Using the new API<\/h3>\n<p>Our new<strong> API<\/strong> enables more sophisticated user queries.<\/p>\n<p>What&#8217;s more, as soon as new data is added to the database, it becomes immediately available to projects built using the API.<\/p>\n<p>So, in many cases, it makes sense to start by creating an <strong>API-like interface<\/strong> for project data before building a data-driven visualization, application or website.<\/p>\n<p>In the next article, we&#8217;ll expand on the techniques presented earlier to see how to program a professional REST API in Python using Flask, OpenAPI (formerly Swagger) and Connexion.<\/p>\n<h3>6. References<\/h3>\n<ul>\n<li><em>Creating Web APIs with Python and Flask, Patrick Smyth : <a href=\"https:\/\/programminghistorian.org\/en\/lessons\/creating-apis-with-python-and-flask\">https:\/\/programminghistorian.org\/en\/lessons\/creating-apis-with-python-and-flask<\/a>.<\/em><\/li>\n<li><em>Flask RESTful documentation : <a href=\"https:\/\/flask-restful.readthedocs.io\/en\/latest\/index.html\">https:\/\/flask-restful.readthedocs.io\/en\/latest\/index.html<\/a>.<\/em><\/li>\n<li><em>Flask Web Development : Developing Web Applications with Python (2\u00e8me \u00e9dition). M. Grinberg. O\u2019Reilly 2018.<\/em><\/li>\n<li><em>Architectural Styles and the Design of Network-Based Software Architectures. T. Fielding. Th\u00e8se, Universit\u00e9 de Californie, 2000.<\/em><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In our first article, we looked at some of the principles involved in building REST APIs, as well as a first example of their implementation in Python using Flask.In this example, data was integrated into the code in the form of a list of dictionaries.We also have an article on how to program and document [&hellip;]<\/p>\n","protected":false},"author":76,"featured_media":172927,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2434],"class_list":["post-172926","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud-dev"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172926","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/76"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=172926"}],"version-history":[{"count":1,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172926\/revisions"}],"predecessor-version":[{"id":206272,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172926\/revisions\/206272"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/172927"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=172926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=172926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}