HomeОбразованиеRelated VideosMore From: sentdex

Read from (SELECT) Database table - SQLite3 with Python 3 part 3

900 ratings | 81344 views
In the previous tutorials, we've covered creating a database and populating one, now we need to learn how to read from the database. Reading from a database is where the power of using something like SQLite over a flat file starts to make sense. While we can query the entire table, we can instead just query a single column, or even based on specific row values. Let's see what I mean sample code and text tutorial: https://pythonprogramming.net/sqlite-part-3-reading-database-python/ Playlist: https://www.youtube.com/playlist?list=PLQVvvaa0QuDezJh0sC5CqXLKZTSKU1YNo https://pythonprogramming.net https://twitter.com/sentdex https://www.facebook.com/pythonprogramming.net/ https://plus.google.com/+sentdex
Html code for embedding videos on your blog
Text Comments (102)
mrphysh (16 days ago)
I am dealing with a sqlite3 database. I want to process data guided by the date stamp column. I see how you are using the unix time stamp but suspect that the date stamp will not work the same. any ideas? (thanks .... good video)
Joseph Saber (23 days ago)
you forgot to talk about reading data from specific row using rowid the full command will be SELECT name FROM UnknownTable WHERE rowid = 1; https://stackoverflow.com/questions/23514348/select-a-specific-index-row-in-sqlite
Jay Kris LIVE (1 month ago)
what if i just want to pull the last entry from the table
Chandru (2 months ago)
But how could I insert bulk data to sql
Nasif I (3 months ago)
8:21 " in MySQL that is not the 'case' " I see what you did there 8)
Syafiqur Rahman (4 months ago)
I cant opent my DB. although I do the same code as sentdex.
D3n N3sm (5 months ago)
Manpreet Singh (5 months ago)
Hi sir i just want to know that when i save some data in database sqlite3 and ID of data is automatically created. i want to print ID of data when i save my data . so how i can do it?
manar saad (5 months ago)
Or I should repeat from def till print?? For each statement
manar saad (5 months ago)
Def read_fro_db(): c.execute("select * from staff") Data=c.fetchall() Print (data) c.execute("select keyword from staff") Da_ta = c.fetchall() Print (da_ta) Is it gonna be like this???
manar saad (5 months ago)
Helpful video!! Thanks. What about if i want to write many queries. How it can be written
Ryan (6 months ago)
How would I use user input to change the logic within the SQL statement?
Malay Kumar (6 months ago)
your videos are very uesful to me .keep it up!
How to get output instead of tuple form
Matthew Taylor (7 months ago)
~9:10 to ignore case in sqlite, COLLATE NOCASE c.execute("SELECT * FROM stufftoplot WHERE value = '2' AND keyword='python' COLLATE NOCASE")
Bhaskarjyoti Tamuly (8 months ago)
But bro, just lyk u mentioned in the beginning part,that SELECT clause only selects that particular piece of data from database,& not prints it...Then how does ur output display the particular part which u selected using 'SELECT * FROM aba WHERE ghjcx :', let's say.. Coz when I'm executing the same statement, it's only giving me the memory location of that particular record...
Ben Morgan (8 months ago)
How do you search for data where for example, a customerID is equal to a variable that a user has entered?
KAWTHAR (9 months ago)
Thank u a lot
Daniel Lozano Rojas (9 months ago)
I don't use to suscribe anyone, but you man, you gained another subscription with your awesome explanations. Please never stop posting.
sentdex (9 months ago)
Woo, thanks for your support!
Mini Project (11 months ago)
My semister project is just possible beacuse of you ...subscribed:) :)
Danny McHenry (11 months ago)
How can I parse the existing date from a table in an sqlite database using python in readable format. When I parse out the date, it is just a string of random numbers.
Francois Smit (11 months ago)
I don't understand what Unix is and why unix is used to obtain the datestamp. Can someoen please explain?
neil ruhter (1 year ago)
How would you pull only one object from my table like a name and I only want just one name to be printed when I hit a button in python 3.6.4
Asfi Reza Pranto (1 year ago)
def read_file(): i = 893 c.execute("SELECT * FROM stuffToPlot WHERE Accnumber = ?"(i)[I just want to use another variable there,which I named "i".But it is not working.Please help.What is the perfect code] data = c.fetchall() print(data)
lotusincense (1 year ago)
an old fellow that has forgotten how to code here! I recently decided to continue my career in data, im listening to your video like a podcast for refreshing my memory. Thank you for the great content man!
Purifier (1 year ago)
Janski (1 year ago)
That SELECT explanation got you a new subscriber :) Very helpful stuff here.
sentdex (1 year ago)
Welcome to the club :D
Dhaval Depala (1 year ago)
what is the ' def read_from_db '
Abhishek Amin (1 year ago)
that is just a method that reads the data from the database. if you're not seeing any data, that's probably because you missed "conn.commit()" in "dynamic_data_entry()" method
Dhaval Depala (1 year ago)
because I can't display my data and i just im just guessing that this is the problem
Ciel Vermillion (1 year ago)
Hey man I'm using pycharm and i can't see the output of my work when i press run all it shows is "Process finished with exit code 0"
Paul Gavett (1 year ago)
Just for people that are following along: at 3:31 you would have to call the function before running or your data won't show up.
Patrick Byrne (1 year ago)
I just spent three hours scratching my head over this. Clutch post.
Nessie Nono (1 year ago)
thank you edward snowden
Kazimierz Osinski (1 month ago)
sentdex (1 year ago)
Happy to share
Kleber Campos (1 year ago)
how to write String with caracter ~´...?
esp heroz (1 year ago)
how you match the keyword with variable outside the syntax? i tried this key = 'Python' c.execute("SELECT keyword, value FROM stuffToPlot WHERE keyword=?",(key)) but i got error Traceback (most recent call last): File "D:/Program_Files/Python/Tutorial/Python_project/database/legit3.py", line 41, in <module> read_from_db() File "D:/Program_Files/Python/Tutorial/Python_project/database/legit3.py", line 34, in read_from_db c.execute("SELECT keyword, value FROM stuffToPlot WHERE keyword=?",(key)) sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 6 supplied.
Mikhil Mistry (1 year ago)
Hey Harrison, could you tell me where did you get the Python cap and hoodie from? I literally scraped the entire Internet and I couldn't find it anywhere!!!
Pratham Singh (1 year ago)
it's on his own website :P https://pythonprogramming.net/store/
mahesh rayudu (1 year ago)
How to write into csv
Siddharth Sonone (1 year ago)
BEST tutorial! If we only had teachers like him around! ;-(
Kelechi Ogudu (1 year ago)
Bro your awesome! Learnt so much from your channel thus far
Nicola Manzini (1 year ago)
I exactly named it sqlite3.py Went crazy for half an hour. then solved thanks to stack overflow.
alwayssmiler112 (1 year ago)
Mysql will act the same with your where clause and will not return any rows if you keep the case of the letters in query different from case of the letters in database. sql itself is not case sensitive but the data is.
Okwukwe Ewurum (1 year ago)
Thanks a lot man. Your explanation is excellent. Is it possible to format the output when you retrieve data from the db file using string formatting function to make the data display like a table without the brackets and quotations?
Osmir Junior (1 year ago)
How to make database app like this run in another computer? This new computer must have SQLite3 installed? How can I (if possible) run this without forcing the user to install a database software?
The Dardans (1 year ago)
turn it into an executable , like turn it into an .exe file.. look a bit in the tutorials and you will see one how to make a file into an exe file.
kray97 (1 year ago)
Are there record and field count limitations with SQLite?
kray97 (1 year ago)
Enjoying your channel a lot - sentdex. Thank you!
Zelania (1 year ago)
Anyone knows how you would write a SELECT FROM Users WHERE name=%s? How can I put in a variable for the %s if i am using a MySQL? Would appreciate any answer, thanks.
Greg F (1 year ago)
In all SQL, text-based select values must be either converted to upper or lower otherwise the value you are selecting is case sensitive. Some databases allow you to turn case-sensitivity off (COLLATE in SQLITE) but otherwise, 'PYTHON' and 'Python' and 'python' are literal. So you might do SELECT * FROM TABLE WHERE LOWER(KEYWORD) = 'python' ...or SELECT * FROM TABLE WHERE UPPER(KEYWORD) = 'PYTHON' ... or SELECT * FROM TABLE WHERE KEYWORD = 'python' COLLATE NOCASE I would use COLLATE NOCASE in preference to UPPER() or LOWER() as they probably have a slight conversion overhead -- as _any_ string conversion function does -- whereas COLLATE NOCASE is perhaps more efficient.
Human Code (1 year ago)
i just wanna point something that you've done: normally you would change the variable name 'data' to 'row' and then when you iterate through the rows you would say of course col[0] not row[0] so all the watchers can understand correctly
Kshitij Pratihast (1 year ago)
How to display the column names in the result set as well ?
Cesar Cuevas (2 years ago)
Great content, thank you
Ezha Randall (2 years ago)
You're my hero! Thanks man!
Sir, can we compare direct date in format (y/m/d) in python and extracted date from SQLite also in same format.
Kevin Aviles (2 years ago)
how would you display the data(images) as icons on tkinter?
Fernando Silva (2 years ago)
Hello sentdex, I am Brazilian, so excuse my English. I wonder how do I do a search in this database, using an external variable. For example: def ProcuraPeca (self, nomepeca): cursor.execute ( "SELECT * FROM WHERE name = ?" (nomepeca)) for row in cursor.fetchall (): print (row) Already I tried everything, however without success. You know help me? thank you so much
Very informative. Thank you very much.
sentdex (2 years ago)
+ErrorAcct001 ErrorAcct001 happy to share!
Ciaran Myers (2 years ago)
Thanks a lot, really helped! just one question though; instead of reading and fetching data from the database, how would i go about reading the database and just checking that a certain piece of data is in there? I have created a database for a login system holding emails, passwords etc and would like the user to say whether or not they already have a login. At the moment users can create a login, but if they say they already have one, i would like to read through the database and check that the email they have typed in is in there and if their password matches up with the one stored with their email. Any help would be great, thanks again.
YoSophia __ (1 year ago)
Doing a similar thing, please message me if you have found a way xx thanks!
CengDev (2 years ago)
you are very smart, Thank you
David Henry (2 years ago)
@SentDex, Have you got any tutorials on using python dataset? Looks much more friendly than SQL. Love the videos!
Josef R (2 years ago)
Hey sentdex, what would you recomend for a database type, if you would be creating a large image database, where the images must be quickly callable ? Thanks in advance!
David Henry (2 years ago)
Do not store images inside a database. Store the images in a folder with a UUID reference and then put the UUID reference in the db. Then pick the db you like the most as they would all be equally fast serving just the picture uuid.jpg name or whatever.
how to read from the database from a variable,just like writing you have done.
章几回 (2 years ago)
thanks for your SQLite3 tutorial,it's very Amazing and useful! thanks again!!!
Nick Theone (2 years ago)
Great videos about sqlite!
sentdex (2 years ago)
Pawel Bartusiak (2 years ago)
0:34 (CTRL + '/') for PyCharm CE 2016
Kostas Nikolouts (1 year ago)
I was on the way to google it.Thank you friend.
Lionel Tellem (2 years ago)
Thanks dude, u saved me efficiency time by 40%.
Martin Baltuhin (2 years ago)
Dude, you are pure gold! My personal Python guru! Best explanations for python newbies! Keep up!
Candela Érika García (2 years ago)
Your explanations are great :D
sentdex (2 years ago)
Sagnik Sarkar (2 years ago)
I am using Pycharm and I am unable to read from the database. The database elements is not showing in the console. The code is compiling correctly.
gordongekko67 (2 years ago)
Thanks a lot for your very interesting videos. they are very useful and very interesting, I have learned Python very well and easy . Compliments ! you are a very clever programmer and teacher. All the best regards Enrico from Italy
sentdex (2 years ago)
Thanks for the kind words Enrico!
Gyan (2 years ago)
querries that u ran are similar to sql just thinking how to refer to two diffrent tables and running join querry and then fetching the data a through python and storing it in excel
Omar ElKhatib (2 years ago)
Keep Going , you are amazing :)
and then i said (2 years ago)
your enter key must be terrified of you.
and then i said (2 years ago)
+Candela Érika García jajaj El horror en su mirada cada que ve un ':' en Python debe ser milenario. - def foo(bar): #AAAAARGH  !!
Candela Érika García (2 years ago)
hahaha I have thought the same when I heard the sound!
and then i said (2 years ago)
+red bee why?
Bree Dreams (2 years ago)
These are such great tutorials and I really need this info, but every time you hit the enter key it is so loud that I cringe. The actual typing isn't too terrible, but the slamming on that enter key gets picked up by the mic pretty loudly. Can you please do something about this?
Bree Dreams (2 years ago)
Oh my gosh, thank you so much!
sentdex (2 years ago)
+Bree Dreams If the videos are causing you discomfort, you can just breeze through the text-versions here: https://pythonprogramming.net/sql-database-python-part-1-inserting-database/
Samuel Pulis (2 years ago)
3:16 just a bunch of goop
sentdex (2 years ago)
+Samuel Pulis It is a technical term :D
Ray Nets (2 years ago)
I tried creating a function where I send a value (name) and using the ? from the previous video I get errors. Might be nice if you show how to make SELECT statements dynamic. c.execute("SELECT playerBank FROM player WHERE playerName = '?'", name) sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 3 supplied.
LightswrathUltimatum (2 years ago)
Thanks man you helped me with my code. I was trying to fix it for over an hour. Came across this and then bam. Straightface..... So happy but for some reason my face is static.
CPSuperstore Inc. (2 years ago)
c.execute("SELECT * FROM table WHERE column=?", (variable,)) You need a comma after the variable, which is in brackets http://stackoverflow.com/questions/13880786/python-sqlite3-string-variable-in-execute
Sandy Vera (2 years ago)
Hi how can I read from SELECT database table using an entry data field typed  by the end user to make a query.This data entry is going to be ask to the end user to make the query to obtain the database info with this value.
Josh (2 years ago)
Yes, I would also like to know how to check if a value exists in the database
Chad Nuttall (2 years ago)
Just an opinion on how to improve your presentation with just a little bit of extra effort. Areas where you make incorrect statements, you could put a little bubble with text correcting your statement and not have to remake an entire video while still avoiding confusion. Just my opinion on taking your videos to another level. They are fantastic though!
Gerardo Herrera (3 years ago)
About case in SQlite the content of a text variable is case sensitive but the commands like 'SELECT' aren't.
Mark Chogall (3 years ago)
+sentdex. To ignore case sensitivity when doing your querries just use: COLLATE NOCASE. With it you can do things like these: c.execute("SELECT * FROM stuffToPlot WHERE value=3 AND keyword='python' COLLATE NOCASE") c.execute("SELECT * FROM stuffToPlot WHERE value=3 AND keyword='PyThOn' COLLATE NOCASE") And get the results you wanted, no matter the way you wrote the word python.
sentdex (3 years ago)
+Gerardo Herrera remedied my statement later when I tested it. MySQL doesn't care, but apparently SQLite does!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.