Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Afloarei Andrei
5,163 PointsCan someone please explain why this works.
Hello.
I am trying to make a journal desktop app in python using sqlite3 as database.
I have one table named 'journal', and inside are 'date' and 'entry'.
Below is a sample of code:
import sqlite3
con = sqlite3.connect('project.db')
text = input("What's new: ")
x = "INSERT INTO journal VALUES(CURRENT_TIMESTAMP, ?);"
con.execute(x, [text])
con.commit()
print("Record created successfully")
con.close()
I want to know why this works, because I did it by mistake:
x = "INSERT INTO journal VALUES(CURRENT_TIMESTAMP, ?);"
con.execute(x, [text])
2 Answers
Steven Parker
243,253 PointsI assume you're wondering about the question mark, since the rest seems pretty standard. I found this on a SQLite documentation page:
A question mark that is not followed by a number creates a parameter with a number one greater than the largest parameter number already assigned. If this means the parameter number is greater than
SQLITE_MAX_VARIABLE_NUMBER, it is an error. This parameter format is provided for compatibility with other database engines. But because it is easy to miscount the question marks, the use of this parameter format is discouraged. Programmers are encouraged to use one of the other symbolic formats or the?NNNformat instead.
So as far as I can tell, it's essentially the same thing as putting "NULL" there.
Afloarei Andrei
5,163 PointsThanks for answering so fast. I read the documentation you suggested and it helped a bit. I found the documentation I needed. So the "?" in SQL represents a value to be provided when the query executes and [keyword] - A keyword enclosed in square brackets is an identifier.
So this is another way of formatting strings in python-sqlite3.
x = "INSERT INTO journal VALUES(CURRENT_TIMESTAMP, ?);"
con.execute(x, [text])
#another example
x = "INSERT INTO journal VALUES(?, ?);"
con.execute(x, [time.asctime(time.localtime(time.time())), text])
#and the most used formatting method
con.execute("INSERT INTO journal VALUES('%s', '%s');"
% (time.asctime(time.localtime(time.time())), text))
Steven Parker
243,253 PointsBut remember that "A question mark that is not followed by a number creates a parameter with a number one greater than the largest parameter number already assigned." This means that if you only use question marks by themselves, no number has been assigned, so they return NULL as the value. That's what was happening in this case.
And this is not typical SQL, it's a specific SQLite feature.