How do I insert data with SQLite

Save data in the database: INSERT INTO

There is also a corresponding SQL statement for entering data in the database. This reads. Next, the appropriate table must be specified:

In our example in the table with the name "people":

Now, in the next step, the data ("values") are transferred - the data are written in brackets:

The data is entered in the order we have specified the fields. So in our example: first name, last name, birthday

We want to include our "Johann Wolfgang von Goethe". He was born on August 28, 1749.

We add to our SQL statement accordingly:

So far so good. In this case, not good. Why? The database doesn't know what belongs together: where does the first name end and where does the last name begin. Therefore, data belonging to a field are put in quotation marks. One likes to use single quotes because our SQL statement itself is often created as a string.

Let's try it out:

Whether that fits or not, the response from the database simply tells us. So test it!

Our complete Python code so far:

When running, we don't get an error message. So it seems to have worked. It gets exciting when we read out the data again. If the data comes out exactly like that, then it fits. Or not?

If you can't wait for the comment to be resolved, here's the guy. The following data types are supported in SQLite: INTEGER, REAL, TEXT, BLOB, and NULL. When I come around the corner with DATE, there is no error message but it is effectively saved as TEXT! It is saved, we can only "calculate" with the date after some conversion action via Python.

Use variables for the SQL statement

We have just set up an SQL statement using and passed the data directly into the SQL statement. This will only be effective in the rarest of cases. Most of the time, we have values ​​in variables that have been recorded, for example, via user input.

So let's build our SQL statement using variables. We'll stick to the same example above and add other well-known names to our database that you would always have liked to have in your address list.

And now we build an SQL statement (which is not yet optimal!):

Why is this approach not so good? Such constructions make the database vulnerable to SQL injection. In other words, defective code is introduced from the outside, which can then be executed directly under certain circumstances. In the worst case, the entire database could be deleted or data could be spied on.

Therefore we do not want to hand over the data unchecked. The following structure filters out the biggest problems:

You can see from the top line that the SQL statement can be very long and that you have to scroll to the right. This is of course impractical on the one hand and it does not improve readability on the other. The technique of using 3 quotation marks can help. So Python no longer has a problem if we split our statement into several lines. This is particularly useful for SQL statements, as we can then display the fields and the variables better.

Here the instruction from above is meaningfully broken into several lines:

And now the complete code:

In the following chapter we will save several data sets in one go - not just one as before.

Recommend • Social Bookmarks • Thank you