BirdNET-Pi & MYSQL
Exporting data from Birdnet-pi and importing it into mysql SERVER
Below I will go through the steps on how to export data from a BirdNet-pi and import it into a Ubunut Linux server that has MySql software installed. This will allow you to search and run queries on the data that was gathered from the BirdNet-Pi unit.
Here is one of the servers I repurposed to run my Linux server on with MySql database. I'll use this server to import data from my BirdNet-Pi data collector. It still has enough power and resources to perform this task.
You can learn more benefits of repurposing old IT equipment by following the link here.
Equipment used: Raspberry Pi (BIRDNET)
Server (LINUX Server with MySQL)
General workstation (Transfer files and run Python code)
1) The first thing you'll need to do is export the data from your BirdNet-Pi. You'll need to find the internal web console of your BirdNet-Pi. If you're unsure how to do this you can reference the BirdNet installation guide for reference, you can access that here.
2) Once there you can click the Database Maintenance button has highlighted below.
3) Next you'll be taken to the main page to log into the birds.db database. Simply click Login to access the database.
4) After that you'll need to click export as highlighted below. There is only one database so simply clicking export will select the only database available.
5) In this step you'll need to make sure all the values you see below highlighted are matched. Once they are you can click the EXPORT button. I'm using a windows system so it will automatically save it to your DOWNLOADS folder.
This will EXPORT a file called detections.csv.You'll want to move the detections.csv file from your downloads folder to your Desktop.
6) On the side I have a Python programming code that will convert the detections.csv into a file named import_data.sql
Take the following code and paste it to notepad, do a save as and rename the file to converter.py. Make sure to save it to your Desktop where you placed the detections.csv file. Make sure to change your file path as stated below!!!!
Note: You'll have to change the code slightly so it can run in the same directory that detections.csv is placed. Below is the only thing you have to modify
csv_file_path = 'C:\\Users\\"REPLACE WITH YOUR HOME DIRECTORY & REMOVE DOUBLE QUOTES"\\OneDrive\\Desktop\detections.csv'# CSV file pathcsv_file_path = 'C:\\Users\\"REPLACE WITH YOUR HOME DIRECTORY & REMOVE DOUBLE QUOTES"\\OneDrive\\Desktop\detections.csv'
# MySQL table nametable_name = "Birds"
# SQL output file pathsql_output_file_path = "import_data.sql"
# Open the CSV file for readingwith open(csv_file_path, "r", newline="") as csv_file: csv_reader = csv.reader(csv_file) header = next(csv_reader) # Get the header row
# Generate the SQL script to create the table create_table_sql = f"CREATE TABLE IF NOT EXISTS {table_name} (" for column in header: create_table_sql += f"{column} VARCHAR(255), " create_table_sql = create_table_sql[:-2] + ");\n"
# Generate the SQL script to insert data insert_data_sql = f"INSERT INTO {table_name} ({', '.join(header)}) VALUES\n"
for row in csv_reader: values = [f"'{value}'" for value in row] insert_data_sql += f"({', '.join(values)}),\n"
insert_data_sql = insert_data_sql[:-2] + ";\n"
# Write the SQL script to the output filewith open(sql_output_file_path, "w") as sql_file: sql_file.write(create_table_sql) sql_file.write(insert_data_sql)
print(f"SQL script has been generated and saved to {sql_output_file_path}.")
8) When you have detections.csv and converter.py both on your Desktop (or it can be same directory ) & ( Your python script code is updated to reflect the file directory both were files are placed in) You'll need to access the command line.
You can run the python script (with administrator rights) as seen below (python ./converter.py). If everything is done correctly you'll get
"The script has been generated as saved to import_data.sql"
9) Copy import_data.sql onto your USB drive
9a) Once copied to your thumb drive open up import_data.sql in notepad or other text editor. Delete and retype from line 1 Birds (Date VARCHAR(255) From line 2 Delete (Date, and retype.
Note: The python program adds unseen white space in character spaces, this causes the Date Field not to be displayed properly when running queries in MySql.
10) Next your going to want to get the file import_data.sql on the Linux server. In my cause I will simply save it to a USB stick and mount the USB stick via the command line. You can learn how to do this HERE.
Note: After you mount the USB drive make note of what directory it resides in. If your in the directory that import_data.sql you can run the pwd command to print working directory, if you followed the instructions provided about it should be in the /media/usb folder.
11) Now its time to verify that you have MySQL installed before moving onto next steps. If you don't have MySQL install you can learn how to simply install MySQL HERE.
12) Start MySql by going to the terminal in Linux and typing sudo mysql and hit enter then enter your password when prompted. Your system might show different from what is displayed in the side image.
You'll want see mysql>
13) Type CREATE DATABASE BIRD_DATA; This will create a database called BIRD_DATA for you to use. Press ENTER
14) Type use BIRD_DATA; This will change MySql to use the BIRD_DATA database Press ENTER
15) Type source /media/usb/import_data.sql; Press ENTER
16) Type DESCRIBE Birds; You should see a list of fields in the table Birds Date, Time, Sci_Name_Com_Name, Confidence...... Press ENTER
17) Type SELECT * FROM Birds; This will list all the detections from the Birds table.
The image to the right shows that 55, 756 bird detections where captured.
18) You have successfully installed all detections captured by the BirdNet-Pi and converted the data to be imported into MySql database via the Linux termina.
Here are Field names from the Birds table to the right --->
You can run different queries like the examples shown below this can help extract specific types of data very quickly.
1) This query would return all detections where the bird species is "Blue Jay."
SELECT * FROM Birds WHERE species = 'Blue Jay';
2) For example, to count the total number of detections in your table, you can use: SELECT COUNT(*) FROM Birds;
3) To retrieve detections within a specific date range: SELECT * FROM Birds WHERE date BETWEEN '2023-01-01' AND '2023-12-31';
;
In this last example I can search for all '%Owls%' in the table and display a Confidence rating > then 0.95
SELECT Date, Time, Com_Name, Sci_Name, Confidence FROM Birds WHERE Com_Name LIKE '%Owl%' AND Confidence > '0.95';
You can learn how to do more queries on your data by referencing MySQL tutorials like the one HERE.
Donate
If you've enjoyed exploring my projects and want to see more amazing creations, your support can make a big difference! By contributing, you're helping me continue to innovate and bring even more exciting projects to life. Don't forget to like, subscribe, and follow for updates on the latest developments. Thank you for being a part of this journey!
Click here to make a difference with your donation today!