Tutorial ESP32 Databases to Control Anything Anywhere Section 3 - Creating SQL Table
Tutorial ESP32 Databases to Control Anything Anywhere Section 3 - Creating SQL TableNow that we have our database connected, we need to create a table to store our data. We'll use SQL commands to do this. The first command we'll use is `CREATE TABLE`. This command tells the database to create a new table. We need to give the table a name, and we need to define the columns that the table will have. For this example, let's create a table called "sensors" with the following columns:
`id`: An integer that will be the primary key for the table. This will uniquely identify each row in the table.
`temperature`: A floating-point number that will store the temperature reading from the sensor.
`humidity`: A floating-point number that will store the humidity reading from the sensor.
`timestamp`: A timestamp that will store the time the reading was taken.
Here's the SQL command to create this table:
```sql
CREATE TABLE sensors (
id INTEGER PRIMARY KEY AUTOINCREMENT,
temperature REAL,
humidity REAL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
```
Let's break down this command:
`CREATE TABLE sensors`: This tells the database to create a table named "sensors".
`id INTEGER PRIMARY KEY AUTOINCREMENT`: This defines a column named "id" that will store integers. `PRIMARY KEY` means that this column will be the primary key for the table. `AUTOINCREMENT` means that the database will automatically generate a new value for this column each time a new row is inserted into the table.
`temperature REAL`: This defines a column named "temperature" that will store floating-point numbers (REAL in SQLite).
`humidity REAL`: This defines a column named "humidity" that will store floating-point numbers.
`timestamp DATETIME DEFAULT CURRENT_TIMESTAMP`: This defines a column named "timestamp" that will store dates and times. `DEFAULT CURRENT_TIMESTAMP` means that the database will automatically insert the current date and time into this column each time a new row is inserted into the table if no value is explicitly provided.
We can execute this SQL command using the same Python code we used to connect to the database. We just need to create a cursor object and then call the `execute()` method on the cursor object with the SQL command as an argument. Here's the code:
```python
import sqlite3
Connect to the database
conn = sqlite3.connect('sensors.db')
Create a cursor object
cursor = conn.cursor()
Execute the SQL command to create the table
cursor.execute('''
CREATE TABLE sensors (
id INTEGER PRIMARY KEY AUTOINCREMENT,
temperature REAL,
humidity REAL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
Commit the changes
conn.commit()
Close the connection
conn.close()
```
After running this code, a new table named "sensors" will be created in the "sensors.db" database. Now we're ready to start inserting data into the table. We'll cover that in the next section.
#robot #robotics #robots #robo #robotic #roboticautomation #roboticaeducativa #robotgames #robotica #microcontroller #mikrokontroler #mikrotik #arduino #arduinoproject #esp32 #esp32project #esp32projects