Web Programming with Python: Inserting Data

Welcome to the third tutorial of SQLite where you are going to learn about how to insert data in SQLite table using Python. We are now going to take the same code that we worked on in the last tutorial and then make changes in it to take it further.

This is the code that we are going to work on:

Import sqlite3
conn=sqlite3.connnect(‘tutorial.db’)
c=conn.cursor()
def create_table():
       c.execute(“CREATE TABLE example (Language VARCHAR, Version REAL, skill TEXT”)
conn.close()

All the new code that will be added will be highlighted in bold for better understanding.
We will now create another function:

def enter_data():

We will now again use “c.execute()” function to execute the SQL query for inserting data. The SQL command for inserting data has the following syntax:

INSERT INTO TABLE_NAME VALUES(value1, value2….)

So, for this code we write:

c.execute(“INSERT INTO example VALUES(‘Python’, 2.7, ‘Beginner’)”)

We will now add two more sets of data:

c.execute(“INSERT INTO example VALUES(‘Python’, 3.3, ‘Intermediate’)”)
c.execute(“INSERT INTO example VALUES(‘Python’, 3.4, ‘Expert’)”)

And then call enter_data() function
So, the final piece of code looks like this:

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’)”)

enter_data()

#conn.close()

Now, execute the code. The conn.close() statement has been commented so that you can understand how things work. Now if you go to the source code you will find a tutorial.db.journal file which is a temporary file. This file is created to store the values as they are being inserted into the database. Large applications are often accessed by several users at the same time. What if two people try to enter a post at exact same time? Well, the values are stored in this file and then at the end of the code when we call conn.close() statement the content of the temporary file move into the database.

So, now again if you run the code and remove the comment on the conn.close() statement:

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’)”)

enter_data()

conn.close()

You will not find the temporary file in the database now.

You can also do the following instead of using conn.close():

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()
enter_data()

conn.close() closes the connection, However, you don’t want to close the connection everytime and then reconnect to the database again. So, conn.commit() can be used after the SQL statements have been executed. This is same as edit and save, but not closing the file so that it can be used again. However if you check again you will not see the temporary file.

So, both conn.commit() and conn.close() move the content of the temporary file into the database.So, this is how we can insert hard coded data into the database. But what if we have to enter data dynamically? i.e. the values are passed through a function which is actually the case? We discuss that in the next tutorial on inserting dynamic data.

Online Technology Courses Stone River eLearning

GET YOUR FREE PYTHON EBOOK!

Comments