Wednesday, August 5, 2015

Python: Create a simple Sqlite Database

This tutorial will teach you how to create a simple SQLite (SQLite) database in python. SQLite is pretty much different to the normal 

Step 1:
Create a file called my_database.py

Step 2: 
Import the necessary items that you will need for this.
Since we are going to use an SQLite database. We need to import it. Also just to add some fun to it, lets add the timestamp to every row that we create. In order to use the time, import it as shown in the second statement.
 import sqlite
 import datetime

Step 3:
Create a connection that will create your database. If the database exists, it will connect to it, if not the database will be created. Also create a cursor. The cursor is provided by Python sqlite3 to perform operations such as executing SQLite statements. When connecting we add the name of the database. In this case 'my_database.sqlite'
 conn = sqlite3.connect('my_database.sqlite')  
 cursor = conn.cursor()  

Step 4:
Now that our script is ready. Lets proceed with creating a table. To make things prettier, lets place it in a method.
 def table_create():  
   cursor.execute("CREATE TABLE IF NOT EXISTS my_table("  
           "id INTEGER PRIMARY KEY AUTOINCREMENT,"  
           "name TEXT,"  
           "description TEXT,"  
           "age INTEGER,"  
           "timestamp TEXT"  
           ")")  

Step 5:
Now our table is ready, lets add some dummy data. Again, making it prettier by adding it in a method.
 def insert_dummy_data():  
   timestamp = str(datetime.datetime.now())  
   cursor.execute("INSERT INTO my_table (name, description, age, timestamp) VALUES('Jonathan', 'Software Engineer', 22,'" + timestamp + "')")  
   cursor.execute("INSERT INTO my_table (name, description, age, timestamp) VALUES('Chethiya', 'Marketing Executing', 22,'" + timestamp + "')")  
   cursor.execute("INSERT INTO my_table (name, description, age, timestamp) VALUES('Charith', 'Business Development Executive', 23,'" + timestamp + "')")  
   conn.commit()  

Step 6:
Everything we need to do has been done except for calling our methods. Lets do that now.
 def main():  
   table_create()  
   insert_dummy_data()
  
 if __name__ == "__main__": main()  

Finally:
Execute this in the command prompt
 python my_database.py  


The database should be created in the same directory as your python script. A SQLite database explorer can be used to view the database. I use the SQLite Manager Add on in Firefox.

Always remember write pretty code, and by pretty i mean neat, fast, efficient code! ;)

See the whole script file here: my_database.py 


Feel free to comment :)
Cheers!

No comments:

Post a Comment