battle programmers alliance
Would you like to react to this message? Create an account in a few clicks or log in to continue.

battle programmers allianceLog in

the LivinGrimoire Artificial General Intelligence software design pattern forum

raspberry pi thonny SQLite

power_settings_newLogin to reply

descriptionraspberry pi thonny SQLite Emptyraspberry pi thonny SQLiteThu Mar 14, 2024 1:37 pm

more_horiz
To use SQLite on a Raspberry Pi with Thonny, you can follow these steps:

Open Thonny IDE: If you’re using Raspberry Pi OS with a desktop environment, Thonny comes pre-installed. You can find it under ‘Programming’ in the main menu1.
Install SQLite: If it’s not already installed, you can install SQLite using the package manager with the command:
sudo apt install sqlite3

Create a New Python File: In Thonny, create a new Python file where you’ll write your SQLite code.
Import SQLite3: At the beginning of your Python script, import the sqlite3 module:
Python

import sqlite3
Connect to a Database: Use the connect method to connect to an SQLite database. If the database does not exist, SQLite will create it for you:
Python

Code:

connection = sqlite3.connect('example.db')
cursor = connection.cursor()

Execute SQL Commands: You can now execute SQL commands using the cursor object. For example, to create a table:
Python

Code:

cursor.execute('''CREATE TABLE IF NOT EXISTS stocks
                 (date text, trans text, symbol text, qty real, price real)''')

Commit Changes: After executing your commands, commit the changes to the database:
Python

connection.commit()
Close the Connection: Once you’re done with the database operations, close the connection:
Python

connection.close()
Remember to replace 'example.db' with the path to your actual database file. If you encounter any issues, make sure the path to the database is correct and accessible.

https://raspberrytips.com/thonny-ide-raspberry-pi/

Last edited by Moti Barski on Thu Mar 14, 2024 1:40 pm; edited 1 time in total

descriptionraspberry pi thonny SQLite EmptyRe: raspberry pi thonny SQLiteThu Mar 14, 2024 1:38 pm

more_horiz
o determine the database path in SQLite when using Thonny on your Raspberry Pi, you can follow these steps:

Default Directory: By default, if you provide just the database name (like ‘example.db’), SQLite will create the database in the current working directory of your Thonny project.
Check Current Working Directory: You can find out the current working directory by running the following command in a Thonny Python script:
Python

Code:

import os
print(os.getcwd())

This will print the path of the directory where your Python script is running, which is also where your database file will be located if you didn’t specify a path.
Specify Full Path: If you want to specify a different location for your database, you can provide the full path when connecting:
Python

Code:

connection = sqlite3.connect('/path/to/your/database/example.db')

Replace /path/to/your/database/ with the actual path where you want to store your database.
Relative Path: Alternatively, you can use a relative path from your script’s directory:
Python

Code:

connection = sqlite3.connect('../databases/example.db')

This example assumes there is a ‘databases’ folder one level up from your script’s directory.
Remember, if you’re using a relative path, it’s relative to the location from where you run your Thonny IDE. If you move your script to a different directory, you’ll need to adjust the path accordingly. Always ensure that the path you specify is accessible and writable by the user running the Thonny IDE.

descriptionraspberry pi thonny SQLite EmptyRe: raspberry pi thonny SQLiteThu Mar 14, 2024 1:45 pm

more_horiz
Here are examples of how to perform create, insert, update, and delete operations using SQLite in Thonny, a Python IDE:

Create Table

Python

Code:

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create a new table
c.execute('''CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            joined DATE NOT NULL)''')

# Save (commit) the changes and close the connection
conn.commit()
conn.close()

Insert Data

Python

Code:

import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Insert a new record
c.execute("INSERT INTO users (username, email, joined) VALUES ('john_doe', 'john@example.com', '2021-01-01')")

# Commit and close
conn.commit()
conn.close()

Update Data

Python

Code:

import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Update an existing record
c.execute("UPDATE users SET email = 'john_doe_updated@example.com' WHERE username = 'john_doe'")

# Commit and close
conn.commit()
conn.close()

Delete Data

Python

Code:

import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Delete a record
c.execute("DELETE FROM users WHERE username = 'john_doe'")

# Commit and close
conn.commit()
conn.close()
privacy_tip Permissions in this forum:
You cannot reply to topics in this forum
power_settings_newLogin to reply