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
- Which is the best barefoot running shoe
- Catch 22 is difficult to read
- How do I reduce my dandruff
- Is wheat a stalk or a seed
- How do I warm a coyote pup
- What is the easiest card game to learn
- What does secondary market mean
- Shouldn't Hitler fight in World War II
- What makes effective texts
- What is 7075 aluminum sheet
- What uses does buckminster fullerene
- Why are phone batteries sometimes heated
- The Bible has been overflowing
- Can my car battery get wet?
- How is the National Grid PLC financed
- Why are most people democratic
- Why is Magnesium Chloride a Lewis Acid
- Smarter people are better drivers
- The Mig 29s engine starts itself
- A startup can give away free alcohol
- What is Trage Woodworking
- Which laptop is good for big data
- Which fruit name has only three letters
- What double standards do men often have
- What is SC test in the transformer
- What is the most successful song from culture clubs
- How is Hillary Clinton misunderstood?
- How are iBooks and MacBooks different
- How long should a sound check take
- What is biocomputing
- What would your perfect truck stop be?
- What is imperfect competition
- Colostrum kills good bacteria
- Is the PESIT South Campus autonomous