Web Programming with Python: Reading Data
Welcome to the tutorial on Reading Data from SQLite. Reading data from the database is important in order to access and verify the details that have been stored in it already. We will be continuing with the same code that we have used in the previous tutorials.
To read data we will define the following function:
def read_data_from_database():
In next step we write the SQL query to read all the data from the example table. The data base query is:
SELECT * FROM example.
Here * stands for ‘ALL’. In python we will assign this command to a variable as follows:
sql = “SELECT * FROM example”
Now to retrieve data from the table we will have to access each row. Hence, we will use the ‘for’ loop as shown below:
for row in c.execute(sql): print (row)
To get a particular value from row you can use indexing. So, in the above function add the following statement:
print(row[0])
So, the final function will look as follows:
def read_data_from_database(): sql = “SELECT * FROM example” for row in c.execute(sql): print (row) print(row[0])
The final full code will be as follows:
Import sqlite3 conn=sqlite3.connnect(‘tutorial.db’) c=conn.cursor() def create_table(): c.execute(“CREATE TABLE example (Language VARCHAR, Version REAL, skill TEXT”) def enter_data(): c.execute(“INSERT INTO example VALUES(‘Python’, 2.7, ‘Beginner’)”) c.execute(“INSERT INTO example VALUES(‘Python’, 3.3, ‘Intermediate’)”) c.execute(“INSERT INTO example VALUES(‘Python’, 3.4, ‘Expert’)”) conn.commit() def enter_dynamic_data(): lang=input(“What Language?”) version=float(Input(“What Version?”) skill=input(“What skill level?”) c.execute(“INSERT INTO example(Language, Version,skill) VALUES(?,?,?)”,(lang, version,skill)) conn.commit() def read_data_from_database(): sql = “SELECT * FROM example” for row in c.execute(sql): print (row) print(row[0]) read_data_from_database()
The output for this will be as follows:
(‘Python’, 2.7, ‘Beginner’) Python (‘Python’, 3.3, ‘Intermediate’) Python (‘Python’, 3.4, ‘Expert’) Python
To get a better understanding let’s now try to read the data of only that row where the skill value is ‘Beginner’
For this we will make following changes to the read_data_from_database() function.
def read_data_from_database(): sql = “SELECT * FROM example WHERE skill== ‘Beginner’” for row in c.execute(sql): print (row)
Now if you execute the new code you will get the following output:
(‘Python’, 2.7, ‘Beginner’)
Let’s suppose that you want to read data dynamically for this we will have to do some modifications to the above program. We will now create a function that will prompt the user to enter the value for skill and then the output will be retrieved accordingly.
The read_data_from_database() function will now look like this:
def read_data_from_database(): what_skill = input(“What skill level are we looking for?”) sql = “SELECT * FROM example WHERE skill=?” for row in c.execute(sql, [(what_skill)]): print (row)
If you now execute this function you will be prompted to enter the skill level, enter Beginner .
What skill level are we looking for? Beginner
The following result will be displayed:
(‘Python’, 2.7, ‘Beginner’)
Now suppose you want to search based on two values – skill and language:
def read_data_from_database(): what_skill = input(“What skill level are we looking for?”) what_language = input(“What language?:”) sql = “SELECT * FROM example WHERE skill=? AND Language=?” for row in c.execute(sql, [(what_skill),(what_language)]): print (row)
Now when you execute the code you will be prompted with two questions. Answer as follows:
What skill level are we looking for? Beginner What language?: Python
Your output will be as follows:
(‘Python’, 2.7, ‘Beginner’)
GET YOUR FREE PYTHON EBOOK!