Python packages: SQLite

3 min readSep 7, 2019

It is very quick to build SQL databases using SQLite from pandas dataframe. SQLite together with Flask allows building database, which can be accessed and updated from web browser .

SQLite is built within the Python release itself. There is no need to install anything with PIP-command, so we can directly import it with command:

import sqlite3 as sql

The database is created by defining its table name, column name and its datatype:

To add the data, we simply need to add:

add_csv.to_sql('table1', conn, if_exists='append', index = False)
conn.close()

These few lines of code — are base for creating a new database, which will appear as a new “database.db”-file.

We will add next Flask-package. Flask helps us to run our database as web service with Python and add new data to the database directly from the browser.

I covered Flask in the previous tutorial. It explains the way to add pages, so I here I will only add few specifics for the database web service.

Adding of the two new html-files:

“render” to build form for user to insert data manually
“addrec” to connect to database and add the new data
“list” to show the data available on the database

@app.route('/render')
def dso():
    return render_template('render.html')
@app.route('/addrec',methods=['POST', 'GET'])
def addrec():
    if request.method=='POST':
        try:
            nm=request.form['field1']
            addr=request.form['field2']
            city=request.form['field3']
            pin=request.form['field4']
            with sql.connect("database.db") as con:
                cur = con.cursor()
                cur.execute("INSERT INTO table1(field1,field2,field3,field4) VALUES(?,?,?,?)", (nm,addr,city,pin) )
                con.commit()
                msg ="Records inserted"
        except:
            con.rollback()
            msg ="Error"
        finally:
            return render_template("results.html", msg=msg)
            con.close()
       
@app.route('/list')
def list():
    con=sql.connect('database.db')
    con.row_factory = sql.Row
    cur = con.cursor()
    cur.execute("select * from table1 order by 1")
    rows=cur.fetchall();
    return render_template('list.html', rows=rows)

Our main page will include a link to the “render.html”, where the user will fill the form, which gets sent by the “addrec” to the database, which was already created.

In terms of the html-files, the render.html is html form, which acts as the input interface. Its data is inserted by the “addrec” to the database.

<html>
<form action=" {{ url_for('addrec') }} " method="POST">Name<br>
<input type="text" name="field1" /></br>
Address<br>
<textarea name="field2" ></textarea><br>
City<br>
<input type="text" name="field3" /></br>
<br><input type="submit" name="submit" /></br></form>
</body>
</html>

To visualize the records of the updated database.db, we will create “list.html”:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>List </title> 
    <link rel="stylesheet" href="static/css/styles.css">  
</head> 
<body>
<table border=1>
<thead>
<td><h3>Field 1</h3></td>
<td><h3>Field 2</h3></td>
<td><h3>Field 3</h3></td>
</thead>
{% for row in rows %}
<tr>
<td>{{row["field1"]}}</td>
<td>{{row["field2"]}}</td>
<td>{{ row["field3"]}}</td>
</tr>
{% endfor %}
</table></body>
</html>

Let’s finally run our web application! As per in our prior Flask-tutorial, we just need to type the web address:

Flask web application

The results will be shown:

Conclusion

I hope you enjoyed reading the guideline for SQLite-package. In fact, it took just few seconds to generate the database, which makes it very friendly for the developers, who need to get a service running very quickly.

Similarly as the SQLite-package, we used Flask-package to generate a web service. The web service allowed to upload new data manually. This makes it very powerful, as these basic principles allow building wide variety of web applications using data as its core and get them running within minutes with few lines of Python code!

If you are wondering, in case there is really a need for fancy databases? As data scientists, we already get lot of work done just by using a single spreadsheet of data, which we upload to our pandas dataframe and thus it might not always be so smart to develop complex databases, which then require extra work as are being maintained. I wrote on my own experiences here.

Previous entry: Flask

Python packages: SQLite

Written by Teemu Maatta

No responses yet