Tuesday, 15 July 2014

How to permanently store values in python? sqlite3!

When you start programming, the first thing you understand is that there is a ton of things, problems and solutions that you are going to deal with. And, in many cases, you do not need to study from books (though that is still an important part) to get a grasp of a new function or piece of code because you will have a problem to solve and that piece of code is the solution that you need. Perhaps you would not even remember it had it not occurred in such a way.

This is the case for the sqlite3 module and me. When developing small projects in Python, sooner or later you are going to face the problem of how to permanently store a value of a variable. Now, that is fine if you need to store it while the script is running, but as soon as you shut down your pc or mac, the moment you shut down Python, that value goes lost. Unless it is in the script and it will be read again next time you run it, it will disappear. But what if we want to make Python remember data entered from the user for future use?

As far as I know, there are actually a few options:
-An external .txt file
-The module Pickle (though I know nothing else about it so far)
-A database

I shall discuss the third option.
Even though there are different modules for this option, not all of them are available for Python 3 (I refer to mysql).
The module sqlite3 is a ready-to-use, built-in module which provides a set of instruction to make Python interact with a database.

Let's see how to create a database:

import sqlite3

conn = sqlite3.connect("my_database.db")

This piece of code will connect to the database named my_database or create a new one named my_database in case it does not find the database.

Now you need to create an object (I do not know if that is the correct name, in case it is not please correct me) which will help us executing SQL code:

c = conn.cursor()

Every time you want to interact with the database now you need to use c, for instance, to create a table:

c.execute("CREATE TABLE products (id INTEGER, PRIMARY KEY, 
name text, model text, cost int, price real)"), 

The string in the parenthesis is SQL code.
SQL is the language by which you can interact with a database.
If you are already familiar with SQL syntax then you will not have any problem using this module, if you are not then I suggest you to check out SQL tutorials for the basic commands.

To insert data into a table you can just do like this:
c.execute("INSERT INTO products VALUES ('orange','brand1'
,1,2.3)")

And now the trickiest part (for me): query your database and show data
In order to query your database, you could do like this

query1 = c.execute("SELECT * FROM products")
query1

However, this will NOT display any data on your Python command line!! In order to display data, you need to iterate over query1, like this:

for row in query1:
    print(row)

This will display the results of your query and print them out.

After you are done with your database, do not forget to close the connection. Of course, next time you will have to reopen it again.

conn.close()



Hope this was useful.

No comments:

Post a Comment