Python

Python and sqlite

by

Updated: 06 July 2024

Use Python to read a csv to sqlite, then write out a new csv

import csv, sqlite3, os

# input csv file something like this
"""
1,maths,smith,sally
9,history,blogs,john
4,art,roberts,mike
"""

# a handy function to drop and recreate a database
def newDatabase():
    if os.path.exists('db.sqlite'):
        os.remove('db.sqlite')
    try:
        conn = sqlite3.connect('db.sqlite')
        return conn
    except Error as e:
        print(e)

conn = newDatabase()
cur = conn.cursor()

sql = '''CREATE TABLE students (
    id INTEGER PRIMAY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
    );'''

# create a table in our database
cur.execute(sql)

# populate table with a few of the columns in the csv file
with open('students_raw.csv') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
    for row in readCSV:
        cur.execute('INSERT INTO students(id, first_name, last_name) VALUES(?,?,?)', [row[0], row[3], row[2]])

conn.commit()

cur.execute('SELECT id, first_name, last_name FROM students')
rows = cur.fetchall()

conn.close()

# write the csv file
with open('out_file.csv', mode='w') as out_file:
    out_writer = csv.writer(out_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    for r in rows:
        out_writer.writerow( [r[0], r[1]] )

Python Flask minimal

by

Updated: 08 August 2022

See https://flask.palletsprojects.com/en/2.1.x/quickstart/

Create an environment
python3 -m venv venv

Activate the environment
. venv/bin/activate

Deactivate the environment
deactivate

Install Flask
pip install Flask

hello.py

# Import the Flask class - an instance of this class
# will be a WSGI application.

from flask import Flask

# Next we create an instance of this class. The first
# argument is the name of the application’s module or
# package. __name__ is a convenient shortcut for this
# that is appropriate for most cases. This is needed so
# that Flask knows where to look for resources such as
# templates and static files.

app = Flask(__name__)

@app.route("/")
def hello_world():
    return "<p>Hello, World!</p>"

run
export FLASK_APP=hello
flask run

If debugger disabled or you trust the users on your network, make the server publicly available
flask run --host=0.0.0.0

The debugger allows executing arbitrary Python code from the browser. It is protected by a pin, but still represents a major security risk.
export FLASK_ENV=development
flask run