Connect to SQLite with Python

What is SQLite?

A database is a place where a program stores information in an organised way, like a very neat spreadsheet. SQLite is a tiny database that lives in one single file. It needs no separate server. Python is a popular programming language. It comes with SQLite support built in, through a module called sqlite3.

Before you start

You need Python installed. To check, type this in a terminal. A terminal is a window where you type commands.

python3 --version

If you see a version number, you are ready. If not, ask Hostvento support whether Python is available on your plan.

Steps

1. Create a new file named demo.py.
2. Add this line at the top to load the SQLite module.

   import sqlite3

3. Connect to a database file. If the file does not exist, SQLite makes it.

   conn = sqlite3.connect('mydata.db')
   cur = conn.cursor()

   The cursor is like a pen. You use it to write commands to the database.
4. Create a table. A table is a grid of rows and columns.

   cur.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')

5. Add a row. Use a question mark as a placeholder. This keeps your data safe from bad input.

   cur.execute('INSERT INTO users (name) VALUES (?)', ('Asha',))
   conn.commit()

   The commit line saves your change.
6. Read the rows back.

   cur.execute('SELECT * FROM users')
   for row in cur.fetchall():
       print(row)

7. Close the connection when you finish.

   conn.close()

8. Run the file with python3 demo.py. You should see your row printed.

Good habits

• Always use placeholders (?) instead of pasting text into your command.
• Always call commit() after you add, change or delete data.
• Keep a backup copy of the .db file. It holds all your data.

Quick recap

• SQLite stores data in one file.
• Python has the sqlite3 module built in.
• Connect, make a cursor, run commands, commit, close.
• Use placeholders to stay safe.