Python SQLite example

Adelaide 190 Published: 06/22/2024

Python SQLite example

I'm happy to provide a response in English for you!

Python and SQLite make a fantastic combination for creating database-driven applications. Here's an example code snippet that demonstrates how to interact with a SQLite database using Python:

import sqlite3
Connect to the database

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

Create a cursor object

c = conn.cursor()

Create table

c.execute('''CREATE TABLE stocks


(date text, transacted_name text, symbol text,


quantity integer)''')


print("Table created!")

Insert values

c.execute("INSERT INTO stocks VALUES ('2022-01-01', 'Apple', 'AAPL', 100)")


c.execute("INSERT INTO stocks VALUES ('2022-02-15', 'Microsoft', 'MSFT', 50)")


conn.commit()


print("Data inserted!")

Query the database

c.execute('SELECT * FROM stocks WHERE symbol = ? OR transacted_name = ?',


('AAPL', 'Apple'))


rows = c.fetchall()


for row in rows:


print(row)

Close the connection

conn.close()

Let's walk through this example:

Connect to the database: We use sqlite3.connect('mydatabase.db') to establish a connection to the SQLite database file named mydatabase.db. You can replace this with a file path or a database URL. Create a cursor object: The conn.cursor() method returns a cursor object that enables you to execute SQL queries on the connected database. Create table: We use the c.execute() method with a SQL query string to create a new table named stocks. This table has four columns: date, transacted_name, symbol, and quantity. Insert values: We use c.execute() again to insert three rows into our stocks table. Query the database: In this example, we execute a SQL query that selects all data from the stocks table where either the symbol or transacted_name matches 'AAPL' or 'Apple'. We use the c.fetchall() method to retrieve the result set, which is stored in the rows variable. Close the connection: Finally, we close our database connection using conn.close() to free system resources and properly terminate the database session.

When you run this code, it will create a SQLite database file named mydatabase.db, create a table named stocks, insert some data, and then query the data based on specific criteria.

Python sqlite3 create database

I'll respond in English as per your request. Here's a comprehensive guide to creating a SQLite database using Python:

Why SQLite?

SQLite is a lightweight, self-contained SQL database that can be easily embedded into applications. It's an excellent choice for small projects or prototyping ideas. Since it doesn't require a separate server process, it's ideal for desktop applications or mobile apps.

Setting Up Python and SQLite

To get started, you'll need to have Python installed on your system. You can download the latest version from the official Python website: https://www.python.org/downloads/. Once installed, open your Python IDE (Integrated Development Environment) of choice.

Next, install the sqlite3 module using pip:

pip install sqlite3

Creating a Database

Now that you have Python and SQLite set up, let's create our database. We'll use the following code to demonstrate this:

import sqlite3
Connect to an in-memory database (or a file-based one if you prefer)

conn = sqlite3.connect(':memory:')

Create a cursor object to execute SQL commands

cursor = conn.cursor()

Create a table called "users"

cursor.execute('''


CREATE TABLE users (


id INTEGER PRIMARY KEY,


name TEXT NOT NULL,


email TEXT NOT NULL


);


''')

Commit the changes (important!)

conn.commit()

Close the connection when we're done

conn.close()

Breaking Down the Code

Let's go through what each part of this code does:

import sqlite3: We import the sqlite3 module, which provides an interface to SQLite databases in Python. conn = sqlite3.connect(':memory:'): We connect to a new database using the connect() method. In this case, we're creating an in-memory database by passing ':memory:' as the filename. You can replace this with a file path if you'd like your database to be saved on disk. cursor = conn.cursor(): We create a cursor object using the cursor() method. The cursor is used to execute SQL commands and retrieve results. cursor.execute('CREATE TABLE users ...'): We use the execute() method to execute a SQL command that creates a new table called "users" with three columns: id, name, and email. The SQL syntax follows standard SQLite conventions, so you can refer to the official SQLite documentation for more information. conn.commit(): After creating the table, we commit the changes using the commit() method. This ensures that all changes are persisted in the database. conn.close(): Finally, we close the connection to the database using the close() method.

Conclusion

In this tutorial, you learned how to create a SQLite database in Python using the sqlite3 module. We covered connecting to an in-memory or file-based database, creating a table using SQL syntax, and committing changes. With these basic steps, you can start building small-scale databases for your projects.

Related Articles