Reading table

Let's make code of reading table. You'll learn about 'Using cursor'

import sqlite3

conn = sqlite3.connect('/Users/asd/Desktop/python_basic/database.db')

c = conn.cursor()

you make cursor in variable 'c' that have some function to read.

‧ fetchone() of 'c' can read just next line.

print(c.fetchone())

‧ fetchmany(size=3) of 'c' can read next some line. (if you have used cursor, 'c' is on the next line

print(c.fetchmany(size=3))

‧ fetchall() of 'c' can read all line.

print(c.fetchall())

Can you understand my explanation? Thank you. Then, We can read by 'for' statement. Look next code.

use FOR

rows = c.fetchall()
for row in rows :  
	print(row)

OR without variable

for row in c.fetchall():
	print(row)

WHERE

Why don't we use the 'WHERE' to read specific id of table. There have some way like tuple, dictionary, and so on.

c.execute("SELECT * FROM users WHERE id=?", (3,))
print(c.fetchone())

c.execute("SELECT * FROM users WHERE id='%s'" %3)
print(c.fetchone())

c.execute("SELECT * FROM users WHERE id=:id", {"id":3})
print(c.fetchone())

c.execute("SELECT * FROM users WHERE id IN(?,?)", (3,5))
print(c.fetchall())

c.execute("SELECT * FROM users WHERE id IN('%d','%d')" %(3,5))
print(c.fetchall())

Now you can read specific row in table by 'WHERE'. How flexible!🥰 I think that (?,?) is type of 'tuple'.

WHERE id = a OR name = b

c.execute("SELECT * FROM users WHERE id=2 OR name='kim'")
print(c.fetchall())