Extracting and Uploading BirdNET-Pi Data

SQLite Database on BirdNET-Pi

• BirdNET-Pi uses an SQlite single file database to store all bird data.
• To find the database open the Terminal.
• Enter the following command to find the SQLite database. The name of the database is birds.db
• The results show that the birds.db is located in /home/pi/BirdNET-Pi/scripts/birds.db
• The find command starts in the / - root directory

sudo find / -name birds.db

• Here is a listing of the /home/pi/BirdNET-Pi/scripts directory.
• The birds.db is located in this directory. We will copy this database later.
• There is also a file named birdDB.txt that is almost an exact copy of the birds.db but available as a text file.
• There also other script files ending in the .sh file extension. We can used these to perform specific functions on the BirdNET-Pi.

• Some of the contents of the birdDB.txt is shown below.
• Use the Terminal command more birdDB.txt to show one page of content.
• To exit press q.
• The total number of rows in this text file was more than 100,000 and the file size was 20 MB.

Creating a working Copy of the BirdNET-Pi SQLite Database

• We need to generate python code that will run data extracts of the BirdNET-Pi database.
• The easiest way to do this is to copy the birds.db to another remote Raspberry Pi computer. Then we can test the python code on a copy of the original database.
• To access the BirdNET-Pi from a remote computer we need to find the IP address of the BirdNET-Pi computer.
• To find the IP address enter the following command in the Terminal.
• The IP address is '192.168.1.133 on this local network.

hostname -I

Secure Copy Database to Remove Computer

• To copy the birds.db to the remote computer the copying needs to be done on the remote computer.
• On the remote computer open the Terminal.
• Enter the following command in the Terminal to copy the file birds.db
  • scp - secure copy command
  • pi - user on the BirdNET-Pi computer
  • 192.168.1.133 - IP address of the BirdNET-Pi computer on the local network
  • ~BirdNET-Pi/scripts/birds.db - path and file name on the BirdNET-Pi computer
  • . - copy the file to the current directory on the remote computer

scp pi@192.168.1.133:~BirdNET-Pi/scripts/birds.db .

• To see if the birds.db has been copied to the remote computer, enter the ls command in the Terminal.

Install DB Browser for SQLite

• The application DB Browser for SQLite can be installed on the Raspberry Pi to view the contents of an SQLite database.
• Install the DB Browser for SQLite with the following commands in the Terminal.

sudo apt update
sudo apt install squlitebrowser

• To Open SQLite Browser navigate to Programs > DB Browser for SQLite or Accessories

• Open the DB Browser for SQLite.
• Click on Open Dabase
• Open birds.db

• Click on the Tables > detections - this is the table that contains all the BirdNET observation data.
• The click on the Browse Data tab. This will allow the database data to be viewed.

• The data in the detections table can be viewed.
• There are 54,470 records in this database table.
• Data columns include Date, Time, Sci_Name, Com_Name, Lat, etc

• Always be sure to Close the database.
• SQLite can be accessed by multiple users at the same time, but this is not advised.
• It is designed to be a self-contained, single-user database engine and is not intended to support concurrent access from multiple users simultaneously.
• However, if your use case involves a single-user application or scenarios where you don't need heavy concurrent access, SQLite can be a lightweight and efficient option.
• Keep in mind that if you attempt to access an SQLite database from multiple processes or threads simultaneously, you might encounter issues related to data integrity and locking.

How to access a SQLite database using Python

To access an SQLite database in Python, you can use the built-in sqlite3 module. This module provides a straightforward way to interact with SQLite databases using SQL queries. Here's a step-by-step guide on how to access an SQLite database in Python:

Import the sqlite3 Module:

Start by importing the sqlite3 module in your Python script:

import sqlite3

Connect to the Database:

Use the connect() function to establish a connection to an SQLite database. If the database doesn't exist, this function will create it.

1. Connect to or create an SQLite database

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

Create a Cursor:

A cursor is used to execute SQL queries and fetch results. Create a cursor using the cursor() method of the connection.

# Create a cursor
cursor = conn.cursor()

Execute SQL Queries:

You can now execute SQL queries using the execute() method of the cursor.

# Execute a CREATE TABLE query
create_table_query = '''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT,
    email TEXT
)
'''
cursor.execute(create_table_query)

Commit Changes and Close Connection:

After executing your queries, don't forget to commit the changes using the commit() method. Finally, close the connection when you're done.

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

Querying Data:

You can also retrieve data from the database using the cursor's execute() method along with the fetchall() or fetchone() methods to retrieve results.

# Query data
select_query = 'SELECT * FROM users'
cursor.execute(select_query)
rows = cursor.fetchall()

for row in rows:
    print(row)

Remember that this is just a basic example. You can perform various SQL operations like INSERT, UPDATE, DELETE, and complex SELECT queries using the execute() method.

Always ensure you handle errors properly and sanitize any user input to prevent SQL injection vulnerabilities.

These steps should help you get started with accessing an SQLite database in Python using the sqlite3 module.

Access the copy of the birds.db

• Python can also access the SQLite database.
• In this example will be read the detections table in a copy of the birds.db
• The copy of the birds.db is found at /home/pi/BirdDB_test/birds.db
• Enter the following code in Thonny and then run the code.
• This code will select all data entries matching the date 2023-08-07
• It will then print each row.
• Note that each row is a python tuple type.

import sqlite3
conn = sqlite3.connect('/home/pi/BirdDB_test/birds.db')
cursor = conn.cursor()

# Example: Query records in table for a specific date
target_date = '2023-08-07'
select_query = 'SELECT * FROM detections WHERE Date = ?'
cursor.execute(select_query, (taget_date,))
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the connection to database
conn.close()

Save a Copy of the Data Output to a Text File

• Each row of data is in the form of a tuple and some items in the tuple are String and numeric.
• All data entries need to be converted to type String.
• To convert all items in a tuple (both strings and numeric values) to a comma-delimited string in Python 3, you can use a combination of the str() function, list comprehension, and the join() method. Here's how you can do it:

my_tuple = ('apple', 42, 'banana', 3.14, 'cherry', 7)

# Convert all items to strings using list comprehension
stringified_items = [str(item) for item in my_tuple]

# Join the stringified items with commas
result = ', '.join(stringified_items)

print(result)

In this example:

• my_tuple is the input tuple containing a mix of strings and numeric values.
• The list comprehension [str(item) for item in my_tuple] converts each item in the tuple to its string representation.
• The join() method is then used to join the stringified items using commas and spaces to create the final comma-delimited string.
• Keep in mind that when using this approach, all items in the tuple will be converted to strings.
• The complete code to save the birds.db is included below.

import sqlite3
conn = sqlite3.connect('/home/pi/BirdDB_test/birds.db')
cursor = conn.cursor()

# Example: Query records in table for a specific date
target_date = '2023-08-07'
select_query = 'SELECT * FROM detections WHERE Date = ?'
cursor.execute(select_query, (taget_date,))
rows = cursor.fetchall()

# Close the connection to database
conn.close()

# Save birdNET detection data to local file named birdDB_listing.txt
data = ''
f = open('/home/pi/BirdDB_test/birdDB_listing.txt','a')
for row in rows:
    # Convert all items to strings using list comprehension
    stringified_items = [str(item) for item in row]
    # Join the stringified items with commas
    data = ', '.join(stringified_items)
    f.write(data + '\n')
f.close()

• The saved data is saved in the file birdDB_listing.txt

Extract data from SQLite database using date

To read data from an SQLite database for dates using Python, you can follow these steps. I'll provide an example of how to query data based on date values:

Assuming you have a table named "records" with a column named "date" that stores date values:

import sqlite3

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

# Example: Query records for a specific date
target_date = '2023-08-07'
select_query = 'SELECT * FROM records WHERE date = ?'
cursor.execute(select_query, (target_date,))
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the connection
conn.close()

In this example:

• Replace your_database.db with the actual path or name of your SQLite database.
• target_date is the date you want to query. You can modify this variable to query data for different dates.
• The SQL query uses a parameterized query (?) and a tuple (target_date,) to safely pass the date value to the query. This helps prevent SQL injection.

• Remember that SQLite stores dates as text or integer values. Common formats include 'YYYY-MM-DD' for text and Unix timestamps (integer). Depending on how your dates are stored, you might need to adjust the query and data handling accordingly.

If your date values are stored in a different format or you want to query for a range of dates, you'll need to modify the query accordingly. The above example is just a starting point for querying date-based data from an SQLite database using Python.

Extract just the hour and minute data entries by parsing using python3

You can extract the hour and minute data entries from a string in the format hour:minute:second using Python by splitting the string and then accessing the relevant parts. Here's how you can do it:

time_string = "12:34:56"  # Replace with your actual time string

# Split the time string into parts using the ":" delimiter
hour, minute, second = time_string.split(":")  

# Convert hour and minute to integers
hour = int(hour)
minute = int(minute)

print("Hour:", hour)
print("Minute:", minute)

Replace "12:34:56" with your actual time string. This code will split the time string into parts using the colon ":" delimiter and then convert the hour and minute parts to integers.

After running this code, you'll have the hour and minute data entries extracted from the time string.

Extract the first or second element in a tuple using python

In Python, you can easily access the elements of a tuple using indexing. Tuples are zero-indexed, meaning the first element has an index of 0, the second element has an index of 1, and so on. Here's how you can extract the first and second elements of a tuple:

my_tuple = (42, "hello", 3.14, "world")

# Extract the first element (index 0)
first_element = my_tuple[0]

# Extract the second element (index 1)
second_element = my_tuple[1]

print("First Element:", first_element)
print("Second Element:", second_element)

In this example, the tuple my_tuple contains elements of different types. The indexing [0] retrieves the first element, and [1] retrieves the second element. Replace the elements in my_tuple with your actual tuple data.

Remember that if the tuple is empty or if you try to access an index that is out of range, you'll get an IndexError exception. Always ensure that your indexing is within the valid range of the tuple.

Using datetime and strftime to extract current date

You can use the datetime module in Python to work with dates and times. The strftime method is used to format a datetime object as a string. Here's how you can extract the current date and format it using strftime:

from datetime import datetime

# Get the current date and time
current_datetime = datetime.now()

# Extract the current date (year, month, and day)
current_date = current_datetime.date()

# Format the current date as a string using strftime
formatted_date = current_date.strftime("%Y-%m-%d")

print("Current Date:", formatted_date)

In this example:

• The datetime.now() function returns the current date and time as a datetime object.
• The .date() method extracts only the date part from the datetime object.
• The .strftime(format) method is used to format the datetime object as a string using the specified format string. %Y represents the year, %m represents the month, and %d represents the day. You can adjust the format string as needed.
• Running this code will print the current date in the format "YYYY-MM-DD". Replace the format string with your desired format if you want a different output format.

Compressing a file to tar.gz using python

You can compress a file to a tar.gz (Gzip-compressed tarball) archive using the tarfile module in Python. Here's how you can do it:

import tarfile

# File to be compressed
source_file = 'file_to_compress.txt'

# Name of the resulting tar.gz archive
archive_name = 'compressed_archive.tar.gz'

# Create the tar.gz archive
with tarfile.open(archive_name, 'w:gz') as tar:
    tar.add(source_file)

print(f'{source_file} compressed to {archive_name}')

In this example:

• Replace file_to_compress.txt with the actual file you want to compress.
• Replace compressed_archive.tar.gz with the desired name for the resulting tar.gz archive.
• The code uses the tarfile.open() context manager with the 'w:gz' mode to create a tar.gz archive for writing.
• The tar.add(source_file) line adds the source file to the archive.
• The 'w:gz' mode specifies that the archive is both written and compressed using Gzip compression.

Run this code, and it will create a tar.gz archive containing the specified file. Make sure the file you want to compress is in the same directory as the script or provide the correct path to the file.

If you need to compress multiple files or directories, you can modify the code to add them to the archive as needed.

How to Create a Dropbox Token so that files can be Uploaded to Dropbox using Python

To establish a Dropbox token for uploading files using Python, you need to use the Dropbox API. The API requires an access token to authenticate your application and interact with Dropbox on behalf of the user. Here's a step-by-step guide on how to set up a Dropbox token for file uploads:

Create a Dropbox App:

• Go to the Dropbox App Console: https://www.dropbox.com/developers/apps/create
• Choose the option to create a new app.

• Select the type of app you want to create (usually Scoped access for most use cases).
• Select Full Dropbox – Access to all files and folders in a user's Dropbox.
• Give your app a name - for example BirdNET-Pi2.

• Once you have created an App you can always find them again using the App Console

Configuring the App

• The App is configured using two Tabs.
  • Settings - to generate an Access token so that a remote computer can upload files to Dropbox
  • Permissions - to configure read and write access to files in Dropbox

App - Permissions

• Adjust Permissions first.
• If you change permission you will need to Regenerate the Access token
• Adjust permissions so that you can read/write files.metatdata and files.content
• When finished click on Submit changes so that changes are committed.

App - Settings

• Use the setting tab to create an Access token
• The access token will give your remote computer the ability to upload files to Dropbox.
• Click on the Generate button to generate an Access Token.
• Once the Access Token is created copy this to your python program.
• Keep the Access Token secret. Do not share with anyone.

Generate an Access Token:

• Once your app is created, navigate to the app's settings in the Dropbox App Console.
• Find the "OAuth 2" section and generate an access token.
• Copy the generated access token; you'll use this to authenticate requests to the Dropbox API.

Install Dropbox Python SDK:

• To make working with the Dropbox API easier, you can use the dropbox Python package. You can install it using pip:

sudo pip3 install dropbox

Upload Files Using the Dropbox SDK:

Here's a basic example of how you can use the Dropbox SDK to upload a file:

import dropbox

ACCESS_TOKEN = 'your_access_token'  # Replace with your actual access token

dbx = dropbox.Dropbox(ACCESS_TOKEN)

# Path to the local file you want to upload
local_file_path = 'path/to/your/file.txt'

# Path in the user's Dropbox where the file will be uploaded
dropbox_path = '/file.txt'

with open(local_file_path, 'rb') as f:
    dbx.files_upload(f.read(), dropbox_path)

print("File uploaded!")

• Replace your_access_token with the access token you generated in the Dropbox App Console.
• Adjust the local_file_path and dropbox_path variables according to your file paths.

Please note that this example demonstrates a basic upload process. Depending on your use case, you might want to add error handling, handle large files, and explore other features provided by the Dropbox API. The Dropbox API documentation can provide more detailed information: https://www.dropbox.com/developers/documentation/python

How to upload a text file to Dropbox

To upload a text file to Dropbox using the files_upload method from the Dropbox Python SDK, you can follow these steps:

• Install Dropbox Python SDK: If you haven't already, install the dropbox Python package using pip: pip install dropbox

pip install dropbox

• Create or Use an Access Token: Make sure you have an access token for your Dropbox app. You can create an app and generate an access token in the Dropbox App Console.
• Write Python Code: Here's an example of how you can upload a text file using the Dropbox Python SDK:

import dropbox

ACCESS_TOKEN = 'your_access_token'  # Replace with your actual access token
FILE_PATH = 'path/to/your/textfile.txt'  # Replace with the path to your text file in your filesystem
DROPBOX_PATH = '/destination_folder/textfile.txt'  # Replace with the desired path in your Dropbox

dbx = dropbox.Dropbox(ACCESS_TOKEN)

with open(FILE_PATH, 'rb') as f:
    dbx.files_upload(f.read(), DROPBOX_PATH)

print("File uploaded!")

Replace your_access_token with your actual access token and adjust FILE_PATH and DROPBOX_PATH to match the file path in your local filesystem and the desired destination path in your Dropbox, respectively.

Remember that this example assumes you've already created the access token with the necessary scopes (such as files.content.write) in your Dropbox app. Always ensure you handle errors properly and check the API documentation for more advanced options or customization.

Zip - Terminal Commands

The zip command in Linux is used to compress files and directories into a zip archive. The basic syntax of the zip command is as follows:

zip [options] archive_name.zip file1 file2 ... directory1 directory2 ...

Here's what each part of the syntax means:

• zip: This is the command itself that you're invoking to create the zip archive.
• [options]: These are optional flags that you can use to customize the behavior of the zip command.
• archive_name.zip: This is the name you want to give to the zip archive file that will be created.
• file1 file2 ...: These are the names of individual files that you want to include in the zip archive. You can list multiple files separated by spaces.
• directory1 directory2 ...: These are the names of directories that you want to include in the zip archive. You can list multiple directories separated by spaces.

Here are a few common examples of using the zip command:

To create a zip archive named "myfiles.zip" containing three files ("file1.txt", "file2.txt", "file3.txt") and a directory ("mydir"):

zip myfiles.zip file1.txt file2.txt file3.txt mydir

To create a zip archive while preserving the directory structure:

zip -r myarchive.zip directory_to_zip

These are just a few examples of how you can use the zip command in Linux. To explore all available options and learn more about the command's usage, you can refer to the command's manual page by typing man zip in the Terminal.

Compressing Files using Zip

• It is more economical to compress files using .zip or .tar.gz to reduce the amount of data transmitted.
• To reduce the size of the birdDB_listing.txt file enter the following command.

zip birdDB_listing.zip birdDB_listing.txt

• With text files this can reduce the file size to approximately 10-15% of the original size.
• One day of data is approximately 130 kBytes uncompressed and 15 kBytes compressed.
• In one year the amount of compressed data requiring transmission is 5.4 MB.

Compressing Files using Zip with Python

You can compress a file to a zip archive in Python using the zipfile module, which provides functionality for creating, reading, and extracting zip archives. Here's an example of how to compress a file to a zip archive using Python:

Here's an example of how to compress a file to a zip archive without using a separate function:

import zipfile

# Specify the source file and the desired zip filename
source_file = 'myfile.txt'  # Replace with your source file's name
zip_filename = 'myarchive.zip'  # Replace with the desired zip archive name

# Create a zip archive and add the source file
with zipfile.ZipFile(zip_filename, 'w', zipfile.ZIP_DEFLATED) as zipf:
    zipf.write(source_file)

print(f'{source_file} compressed to {zip_filename}')

In this example, the with statement is used with zipfile.ZipFile to create a new zip archive. The write method is then used to add the source file to the archive. The ZIP_DEFLATED argument specifies that the data should be compressed using the DEFLATE compression algorithm.

Replace 'myfile.txt' with the name of the file you want to compress and 'myarchive.zip' with the desired name for the zip archive. After running the code, you'll have a zip archive containing the specified file.

Remember to handle any potential exceptions that may occur during file operations. This approach eliminates the need for a separate function and performs the compression directly within the code block.

Create a File Name with the Current Date and Time in Python

You can create a file name in Python that includes the current date and time using the datetime module to get the current date and time, and then formatting it into a string. Here's an example:

import datetime

# Get the current date and time
current_datetime = datetime.datetime.now()

# Format the date and time as a string
formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")

# Create a file name using the formatted date and time
file_name = f"file_{formatted_datetime}.txt"

print("Generated file name:", file_name)

In this example, the strftime method is used to format the current date and time as a string according to the specified format.

• %Y represents the year,
• %m is the month,
• %d is the day,
• %H is the hour,
• %M is the minute, and
• %S is the second.

The resulting file_name will look something like: file_2023-08-07_15-30-45.txt (depending on the current date and time).

You can adjust the format to match your preferences for how you want the date and time to be displayed in the file name.

How to find the date of the previous day using python

You can use the datetime module in Python to find the date of the previous day's calendar date by subtracting one day from the current date. Here's how you can do it:

import datetime

# Get the current date
current_date = datetime.date.today()

# Calculate the previous day's date
previous_day_date = current_date - datetime.timedelta(days=1)

print("Previous day's date:", previous_day_date)

In this example, the datetime.date.today() function is used to get the current date. Then, the datetime.timedelta(days=1) function is used to create a time delta representing one day. By subtracting this time delta from the current date, you obtain the previous day's date.

The previous_day_date variable will contain the date of the previous day's calendar date.

Keep in mind that time zones and daylight saving time changes can affect the accuracy of this calculation, especially if you're working with time-sensitive applications.

Using Cron to run Python Scripts

To schedule the execution of a Python script using crontab and manage its outputs, logs, and errors, you can follow these steps:

Edit the Crontab Configuration:

Open your crontab configuration file for editing by running:

crontab -e

This will open the crontab configuration in the default text editor.

Schedule the Python Script:

Add a new line to the crontab file to schedule the execution of your Python script. The general syntax for scheduling a task is:

* * * * * command_to_run

Here, the five asterisks represent the minute, hour, day of the month, month, and day of the week fields, respectively. Adjust these fields according to your desired schedule. For example, to run the script every day at 3:00 PM, you can use:

0 15 * * * /usr/bin/python3 /path/to/your/script.py

Replace /usr/bin/python3 with the actual path to your Python interpreter and /path/to/your/script.py with the full path to your Python script.

Redirecting Outputs and Logging:

By default, the standard output (stdout) and standard error (stderr) of the command executed by cron are sent to the email address associated with the user's account. To capture these outputs and log them to files, you can use output redirection.

For example, to redirect both stdout and stderr to separate log files, modify the crontab line like this:

0 15 * * * /usr/bin/python3 /path/to/your/script.py > /path/to/output.log 2> /path/to/error.log

Replace /path/to/output.log and /path/to/error.log with the desired file paths for capturing the respective outputs.

Logging to a Single File:

If you prefer to log both stdout and stderr to a single log file, you can use:

0 15 * * * /usr/bin/python3 /path/to/your/script.py > /path/to/logfile.log 2>&1

The 2>&1 part redirects stderr to the same location as stdout.

Save and Exit:

After making the necessary changes, save and exit the crontab configuration file.

The crontab scheduler will then execute your Python script at the specified intervals, and you'll have separate log files or a combined log file capturing the outputs and errors of your script. Make sure to use absolute paths for your script, Python interpreter, and log files to avoid issues with working directory differences between cron and your user environment.