{"id":2519,"date":"2016-07-26T13:35:11","date_gmt":"2016-07-26T04:35:11","guid":{"rendered":"http:\/\/avinton.com\/?page_id=2519"},"modified":"2021-11-09T16:14:24","modified_gmt":"2021-11-09T07:14:24","slug":"postgresql-python-apache-bootstrap","status":"publish","type":"page","link":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/","title":{"rendered":"PostgreSQL &#8211; Python &#8211; Apache &#8211; Bootstrap"},"content":{"rendered":"<div class=\"wpb-content-wrapper\"><p>[vc_row][vc_column][vc_column_text]<\/p>\n<h1>PostgreSQL &#8211; Python &#8211; Apache &#8211; Bootstrap<\/h1>\n<p>&nbsp;<\/p>\n<h2>Simple web application<\/h2>\n<p>In this exercise, we will show some data we have in PostgreSQL on the front end.<br \/>\nFor this, we will query our data from our PostgreSQL database using Python and show it on the front end using Bootstrap for formatting via Apache webserver.<\/p>\n<p>This is an example of using a combination of different technologies we have learned for a simple web application.<\/p>\n<p>&nbsp;<\/p>\n<h3>1. Setup Apache<\/h3>\n<pre class=\"\">sudo apt-get install apache2<\/pre>\n<p>&nbsp;<\/p>\n<h4>Verify service is running:<\/h4>\n<pre class=\"\">service apache2 status<\/pre>\n<p>&nbsp;<\/p>\n<h4>Verify it is working<\/h4>\n<p>Visit <a href=\"http:\/\/localhost\" target=\"_blank\" rel=\"noopener noreferrer\">http:\/\/localhost<\/a> on your virtual machine &#8211; you should see the apache test page.<\/p>\n<p>&nbsp;<\/p>\n<h3>2. Query Data from PostgreSQL using python<\/h3>\n<p>For this we will use the same data we loaded in:<\/p>\n<p>&nbsp;<\/p>\n<h4>Environment<\/h4>\n<pre class=\"\"># Go to home directory\r\ncd ~\r\n\r\n# Create directory for application\r\nmkdir swa\r\n\r\n# Create new python file for our code\r\ntouch swa.py\r\n\r\n# Install Python module to connect to PostgreSQL\r\nsudo apt-get install python-psycopg2\r\n\r\n# Install Setuptools which is required for Flask\r\nsudo pip install setuptools\r\n\r\n# Install Flask - A web framework for Python\r\nsudo pip install Flask\r\n\r\n# Update the postgresql pg_hba.conf file to allow for python to connect to it\r\nvi \/etc\/postgresql\/9.5\/main\/pg_hba.conf\r\n\r\n# make changes highlighted in red below<\/pre>\n<p><img decoding=\"async\" src=\"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png\" alt=\"Peer to Password\" \/><\/p>\n<pre class=\"\"># Restart PostgreSQL Server\r\nsudo service postgresql stop\r\nsudo service postgresql start<\/pre>\n<p>Now the environment is set up.<br \/>\nCreate a Python program that connects to the database &#8211; retrieves the japan_cities table and displays it on a web page using Bootstrap Tables<\/p>\n<h2>Hints:<\/h2>\n<h3>SQL in JSON<\/h3>\n<p>You can use this SQL below to return the data from the database in JSON format:<\/p>\n<pre class=\"\">select\r\njsonb_pretty(to_jsonb(array_agg(json_build_object(\r\n'prefecture', prefecture,\r\n'city', city,\r\n'ward', ward,\r\n'population', population,\r\n'city_ward', city_ward))))\r\nfrom\r\njapan_cities\r\n;<\/pre>\n<h3>Use <a href=\"http:\/\/bootstrap-table.wenzhixin.net.cn\/\">Bootstrap Table<\/a> to display the data<\/h3>\n<h3>Use Flask framework for running the python webservice example:<\/h3>\n<pre class=\"\">#!\/usr\/bin\/python\r\n# -*- coding: utf-8 -*-\r\n\r\n\"\"\"\r\nAvinton python endpoint\r\nswa.py\r\n\"\"\"\r\n\r\nimport sys\r\nimport json\r\nimport psycopg2\r\nimport psycopg2.extensions\r\npsycopg2.extensions.register_type(psycopg2.extensions.UNICODE)\r\npsycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)\r\n\r\nfrom flask import Flask # Import class Flask from module flask\r\nfrom flask_cors import CORS, cross_origin #Import flask-cors to accept access to the data\r\napp = Flask(__name__) # Construct an instance of Flask class\r\nCORS(app) # apply CORS\r\n\r\n@app.route('\/') # Register index() as route handler for root URL '\/'\r\ndef index():\r\n\"\"\"Route handler (or View Function) for root URL '\/'\"\"\"\r\nreturn 'Hello, you have reached the default route for the python endpoint'\r\n\r\n@app.route('\/cities.json')\r\ndef cities():\r\ncon = None\r\njs_string = ''\r\n\r\ntry:\r\ncon = psycopg2.connect(database='avinton', user='postgres')\r\ncur = con.cursor()\r\ncur.execute(\"\"\"&lt;insert your SQL here&gt;\"\"\")\r\n\r\njs_string = str(cur.fetchone()[0])\r\nprint js_string\r\n\r\nexcept psycopg2.DatabaseError, e:\r\nprint 'DB Error %s' % e\r\nsys.exit(1)\r\n\r\nfinally:\r\nif con:\r\ncon.close()\r\n\r\nreturn js_string\r\n\r\n@app.route('\/test')\r\ndef test():\r\nreturn 'test'\r\n\r\nif __name__ == '__main__': # Script executed directly?\r\napp.run() # Launch built-in web server and run this Flask webapp<\/pre>\n<h3>Create the HTML page in the apache2 directory<\/h3>\n<p>(you can see which is this directory from the default page http:\/\/localhost)<\/p>\n<pre class=\"\">&lt;DOCTYPE html&gt;\r\n&lt;html&gt;\r\n&lt;head&gt;\r\n&lt;meta charset=\"utf-8\"&gt;\r\n&lt;meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\"&gt;\r\n&lt;meta name=\"viewport\" content=\"width=device-width, initial-scale=1\"&gt;\r\n&lt;title&gt;Data Table in Bootstrap&lt;\/title&gt;\r\n\r\n~~~import Bootstrap source~~~\r\n&lt;\/head&gt;\r\n\r\n&lt;body&gt;\r\n~~~create table with the data~~~\r\n&lt;\/body&gt;\r\n&lt;\/html&gt;<\/pre>\n<p>Save as <em>index.html<\/em> and put it in apache2 directory.<br \/>\nNow the page shows you the data from Apache server!<br \/>\n<img decoding=\"async\" src=\"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/swa2bstrap.png\" alt=\"bstrapoutput\" \/>[\/vc_column_text][\/vc_column][\/vc_row]<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>[vc_row][vc_column][vc_column_text] PostgreSQL &#8211; Python &#8211; Apache &#8211; Bootstrap &nbsp; Simple web application In this exercise, we will show some data we have in PostgreSQL on the front end. For this, we will query our data from our PostgreSQL database using Python and show it on the front end using Bootstrap for formatting via Apache webserver.<br \/><a href=\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/\" class=\"more\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"parent":1906,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-2519","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>PostgreSQL - Python - Apache - Bootstrap - Avinton Japan<\/title>\n<meta name=\"description\" content=\"PostgreSQL \u2013 Python \u2013 Apache \u2013 Bootstrap: In this exercise we will show you some data we have in a PostgreSQL. Find out more about IT jobs in Japan.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL - Python - Apache - Bootstrap - Avinton Japan\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL \u2013 Python \u2013 Apache \u2013 Bootstrap: In this exercise we will show you some data we have in a PostgreSQL. Find out more about IT jobs in Japan.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/\" \/>\n<meta property=\"og:site_name\" content=\"Avinton Japan\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Avintons\/\" \/>\n<meta property=\"article:modified_time\" content=\"2021-11-09T07:14:24+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@AvintonJapan\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/\",\"url\":\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/\",\"name\":\"PostgreSQL - Python - Apache - Bootstrap - Avinton Japan\",\"isPartOf\":{\"@id\":\"https:\/\/avinton.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png\",\"datePublished\":\"2016-07-26T04:35:11+00:00\",\"dateModified\":\"2021-11-09T07:14:24+00:00\",\"description\":\"PostgreSQL \u2013 Python \u2013 Apache \u2013 Bootstrap: In this exercise we will show you some data we have in a PostgreSQL. Find out more about IT jobs in Japan.\",\"breadcrumb\":{\"@id\":\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#primaryimage\",\"url\":\"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png\",\"contentUrl\":\"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/avinton.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Avinton Academy\",\"item\":\"https:\/\/avinton.com\/en\/academy\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PostgreSQL &#8211; Python &#8211; Apache &#8211; Bootstrap\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/avinton.com\/en\/#website\",\"url\":\"https:\/\/avinton.com\/en\/\",\"name\":\"Avinton Japan\",\"description\":\"Tailored Solutions and Consulting in AI and Big Data\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/avinton.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL - Python - Apache - Bootstrap - Avinton Japan","description":"PostgreSQL \u2013 Python \u2013 Apache \u2013 Bootstrap: In this exercise we will show you some data we have in a PostgreSQL. Find out more about IT jobs in Japan.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL - Python - Apache - Bootstrap - Avinton Japan","og_description":"PostgreSQL \u2013 Python \u2013 Apache \u2013 Bootstrap: In this exercise we will show you some data we have in a PostgreSQL. Find out more about IT jobs in Japan.","og_url":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/","og_site_name":"Avinton Japan","article_publisher":"https:\/\/www.facebook.com\/Avintons\/","article_modified_time":"2021-11-09T07:14:24+00:00","og_image":[{"url":"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_site":"@AvintonJapan","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/","url":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/","name":"PostgreSQL - Python - Apache - Bootstrap - Avinton Japan","isPartOf":{"@id":"https:\/\/avinton.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#primaryimage"},"image":{"@id":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#primaryimage"},"thumbnailUrl":"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png","datePublished":"2016-07-26T04:35:11+00:00","dateModified":"2021-11-09T07:14:24+00:00","description":"PostgreSQL \u2013 Python \u2013 Apache \u2013 Bootstrap: In this exercise we will show you some data we have in a PostgreSQL. Find out more about IT jobs in Japan.","breadcrumb":{"@id":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#primaryimage","url":"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png","contentUrl":"http:\/\/avinton.com\/wp-content\/uploads\/2016\/07\/peertopassword-1.png"},{"@type":"BreadcrumbList","@id":"https:\/\/avinton.com\/en\/academy\/postgresql-python-apache-bootstrap\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/avinton.com\/en\/"},{"@type":"ListItem","position":2,"name":"Avinton Academy","item":"https:\/\/avinton.com\/en\/academy\/"},{"@type":"ListItem","position":3,"name":"PostgreSQL &#8211; Python &#8211; Apache &#8211; Bootstrap"}]},{"@type":"WebSite","@id":"https:\/\/avinton.com\/en\/#website","url":"https:\/\/avinton.com\/en\/","name":"Avinton Japan","description":"Tailored Solutions and Consulting in AI and Big Data","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/avinton.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/pages\/2519","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/comments?post=2519"}],"version-history":[{"count":34,"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/pages\/2519\/revisions"}],"predecessor-version":[{"id":60570,"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/pages\/2519\/revisions\/60570"}],"up":[{"embeddable":true,"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/pages\/1906"}],"wp:attachment":[{"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/media?parent=2519"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/categories?post=2519"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/avinton.com\/en\/wp-json\/wp\/v2\/tags?post=2519"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}