code-boxx/0-PY-SQLITE-TABLE.MD

## 0-PY-SQLITE-TABLE.MD

      
    Raw
  

              0-PY-SQLITE-TABLE.MD
            
          
    PYTHON DISPLAY SQLITE DATA IN HTML TABLE

https://code-boxx.com/sqlite-data-html-table-python-flask/
NOTES


Run unpack.bat (Windows) unpack.sh (Linux/Mac). This will automatically:

Create a templates folder, move S3_users.html inside.
Create a static folder, move S3_users.css inside.
Create a virtual environment - virtualenv venv.
Activate the virtual environment - venv\scripts\activate (Windows) venv/bin/activate (Mac/Linux)
Install Flask - pip install flask
Run python S1B_create.py to create the database.
Run python S2_server.py to start the server.


Access http://localhost in your browser.

LICENSE

Copyright by Code Boxx
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

  
## S1A_users.sql
-- (A) USERS
CREATE TABLE `users` (
  `user_id` INTEGER NOT NULL,
  `user_name` TEXT NOT NULL,
  `user_email` TEXT DEFAULT NULL,
  PRIMARY KEY("user_id" AUTOINCREMENT)
);

-- (B) DUMMY USERS
INSERT INTO `users`
  (`user_name`, `user_email`)
VALUES
  ("Jo Doe", "jo@doe.com"),
  ("Job Doe", "job@doe.com"),
  ("Joe Doe", "joe@doe.com"),
  ("Jog Doe", "jog@doe.com"),
  ("Joi Doe", "joi@doe.com"),
  ("Jol Doe", "jol@doe.com"),
  ("Jon Doe", "jon@doe.com"),
  ("Jos Doe", "jos@doe.com"),
  ("Jou Doe", "jou@doe.com"),
  ("Joy Doe", "joy@doe.com");

## S1B_create.py
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error

# (B) DATABASE + SQL FILE
DBFILE = "users.db"
SQLFILE = "S1A_users.sql"

# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
  os.remove(DBFILE)

# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
with open(SQLFILE) as f:
  conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")

## S2_server.py
# (A) INIT
# (A1) LOAD MODULES
from flask import Flask, render_template, request, make_response
import sqlite3

# (A2) FLASK SETTINGS + INIT
HOST_NAME = "localhost"
HOST_PORT = 80
DBFILE = "users.db"
app = Flask(__name__)
# app.debug = True

# (B) HELPER - GET ALL USERS FROM DATABASE
def getusers():
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()
  cursor.execute("SELECT * FROM `users`")
  results = cursor.fetchall()
  conn.close()
  return results

# (C) DEMO PAGE - SHOW USERS IN TABLE
@app.route("/")
def index():
  # (C1) GET ALL USERS
  users = getusers()
  # print(users)

  # (C2) RENDER HTML PAGE
  return render_template("S3_users.html", usr=users)

# (D) START
if __name__ == "__main__":
  app.run(HOST_NAME, HOST_PORT)

## S3_users.css
* {
  font-family: arial, sans-serif;
  box-sizing: border-box;
}
#demo {
  border-collapse: collapse;
}
#demo tr:nth-child(odd) {
  background: #f2f2f2;
}
#demo td {
  padding: 10px;
}

## S3_users.html
<!DOCTYPE html>
<html>
  <head>
    <title>Users List</title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="static/S3_users.css"/>
  </head>
  <body>
    <table id="demo">
      {% for u in usr %}
      <tr>
        <td>{{ u[0] }}</td>
        <td>{{ u[1] }}</td>
        <td>{{ u[2] }}</td>
      </tr>
      {% endfor %}
    </table>
  </body>
</html>

## unpack.bat
md templates
md static
move S3_users.html templates
move S3_users.css static
virtualenv venv
call venv\Scripts\activate
pip install flask
python S1B_create.py
python S2_server.py

## unpack.sh
mkdir -m 777 templates
mkdir -m 777 static
mv ./S3_users.html /templates
mv ./S3_users.css /static
virtualenv venv
source "venv/bin/activate"
pip install flask
python S1B_create.py
python S2_server.py
	-- (A) USERS
	CREATE TABLE `users` (
	`user_id` INTEGER NOT NULL,
	`user_name` TEXT NOT NULL,
	`user_email` TEXT DEFAULT NULL,
	PRIMARY KEY("user_id" AUTOINCREMENT)
	);

	-- (B) DUMMY USERS
	INSERT INTO `users`
	(`user_name`, `user_email`)
	VALUES
	("Jo Doe", "jo@doe.com"),
	("Job Doe", "job@doe.com"),
	("Joe Doe", "joe@doe.com"),
	("Jog Doe", "jog@doe.com"),
	("Joi Doe", "joi@doe.com"),
	("Jol Doe", "jol@doe.com"),
	("Jon Doe", "jon@doe.com"),
	("Jos Doe", "jos@doe.com"),
	("Jou Doe", "jou@doe.com"),
	("Joy Doe", "joy@doe.com");
	# (A) LOAD PACKAGES
	import sqlite3, os
	from sqlite3 import Error

	# (B) DATABASE + SQL FILE
	DBFILE = "users.db"
	SQLFILE = "S1A_users.sql"

	# (C) DELETE OLD DATABASE IF EXIST
	if os.path.exists(DBFILE):
	os.remove(DBFILE)

	# (D) IMPORT SQL
	conn = sqlite3.connect(DBFILE)
	with open(SQLFILE) as f:
	conn.executescript(f.read())
	conn.commit()
	conn.close()
	print("Database created!")
	# (A) INIT
	# (A1) LOAD MODULES
	from flask import Flask, render_template, request, make_response
	import sqlite3

	# (A2) FLASK SETTINGS + INIT
	HOST_NAME = "localhost"
	HOST_PORT = 80
	DBFILE = "users.db"
	app = Flask(__name__)
	# app.debug = True

	# (B) HELPER - GET ALL USERS FROM DATABASE
	def getusers():
	conn = sqlite3.connect(DBFILE)
	cursor = conn.cursor()
	cursor.execute("SELECT * FROM `users`")
	results = cursor.fetchall()
	conn.close()
	return results

	# (C) DEMO PAGE - SHOW USERS IN TABLE
	@app.route("/")
	def index():
	# (C1) GET ALL USERS
	users = getusers()
	# print(users)

	# (C2) RENDER HTML PAGE
	return render_template("S3_users.html", usr=users)

	# (D) START
	if __name__ == "__main__":
	app.run(HOST_NAME, HOST_PORT)
	* {
	font-family: arial, sans-serif;
	box-sizing: border-box;
	}
	#demo {
	border-collapse: collapse;
	}
	#demo tr:nth-child(odd) {
	background: #f2f2f2;
	}
	#demo td {
	padding: 10px;
	}
	<!DOCTYPE html>
	<html>
	<head>
	<title>Users List</title>
	<meta charset="utf-8">
	<link rel="stylesheet" href="static/S3_users.css"/>
	</head>
	<body>
	<table id="demo">
	{% for u in usr %}
	<tr>
	<td>{{ u[0] }}</td>
	<td>{{ u[1] }}</td>
	<td>{{ u[2] }}</td>
	</tr>
	{% endfor %}
	</table>
	</body>
	</html>
	md templates
	md static
	move S3_users.html templates
	move S3_users.css static
	virtualenv venv
	call venv\Scripts\activate
	pip install flask
	python S1B_create.py
	python S2_server.py
	mkdir -m 777 templates
	mkdir -m 777 static
	mv ./S3_users.html /templates
	mv ./S3_users.css /static
	virtualenv venv
	source "venv/bin/activate"
	pip install flask
	python S1B_create.py
	python S2_server.py