SQLite Backend for Beginners - Create Quick Databases with Python and SQL

Introduction

In this tutorial, we will learn how to create and manage a SQLite database using Python. SQLite is a lightweight database engine that allows for quick and simple database management using SQL, which is more intuitive than many programming languages. By the end of this guide, you'll be able to create databases, insert data, and retrieve information efficiently.

Step 1: Set Up Your Environment

• Install SQLite: Ensure you have SQLite installed on your machine. You can download it from the SQLite official website.
• Install Python: Make sure you have Python installed. You can download it from the Python official website.
• Choose an IDE: Use an IDE like Wayscript for a user-friendly coding environment. Sign up at Wayscript.

Step 2: Create an Empty SQLite Database

• Import the sqlite3 library:

  import sqlite3
  

• Create a connection to a new database file called gta.db:

  conn = sqlite3.connect('gta.db')
  

• Create a cursor object to execute SQL commands:

  cursor = conn.cursor()
  

Step 3: Create a Database Table

• Use the following SQL command to create a table called games:

  cursor.execute('''
      CREATE TABLE games (
          id INTEGER PRIMARY KEY,
          release_year INTEGER,
          title TEXT,
          location TEXT
      )
  ''')
  

• Remember to commit changes to the database:

  conn.commit()
  

Step 4: Understand SQLite Data Types

Familiarize yourself with common SQLite data types

• INTEGER: Whole numbers
• TEXT: Strings
• REAL: Floating-point numbers
• BLOB: Binary data

Step 5: Insert Multiple Rows of Data

• Define a list of tuples with game data:

  release_list = [
      (1997, "Grand Theft Auto", "state of New Guernsey"),
      (1999, "Grand Theft Auto 2", "Anywhere, USA"),
      (2001, "Grand Theft Auto III", "Liberty City"),
      (2002, "Grand Theft Auto: Vice City", "Vice City"),
      (2004, "Grand Theft Auto: San Andreas", "state of San Andreas"),
      (2008, "Grand Theft Auto IV", "Liberty City"),
      (2013, "Grand Theft Auto V", "Los Santos")
  ]
  

• Insert the data into the games table:

  cursor.executemany('''
      INSERT INTO games (release_year, title, location)
      VALUES (?, ?, ?)
  ''', release_list)
  

• Commit the changes to save the data:

  conn.commit()
  

Step 6: Select and Print All Database Rows

• Use a SQL query to fetch all records:

  cursor.execute('SELECT * FROM games')
  rows = cursor.fetchall()
  for row in rows
      print(row)
  

Step 7: Select Specific Rows with Specific Values

• To fetch specific records based on criteria, use:

  cursor.execute('SELECT * FROM games WHERE release_year > 2000')
  filtered_rows = cursor.fetchall()
  for row in filtered_rows
      print(row)
  

Step 8: Combine Multiple Database Tables

• Create additional tables as needed, and use SQL JOIN to combine data:

  SELECT * FROM table1
  JOIN table2 ON table1.common_field = table2.common_field
  

Step 9: Manipulate Data Fetched from Database

• You can manipulate the retrieved data using Python's built-in functions. For example, calculate statistics or filter results programmatically.

Conclusion

In this tutorial, we covered the essentials of creating and managing a SQLite database with Python. You learned how to set up an environment, create a database and table, insert and select data, and manipulate the results. To further enhance your skills, consider exploring more complex SQL queries or integrating SQLite with web applications. Happy coding!