Connecting to Sqlite databases – Yasoob Khalid

Hello there fellas. As we speak i’m going to show you easy methods to use sqlite databases with python. This submit will cowl the fundamentals of creating and utilizing a sqlite database with python utilizing the sqlite3 library. Okay lets get began. Firstly in case you are utilizing python 2.5 or higher then you’ll have sqlite3 put in in any other case you’ll have to set up it.

Creating and connecting to a database

So how do you make a database in python utilizing the sqlite3 library? It’s fairly easy. Simply observe the code beneath and it is possible for you to to make it out by yourself.

#!/usr/bin/python

import sqlite3

# If the database is already created then this
# code will hook up with it as a substitute of creating a brand new one
conn = sqlite3.join('check.db')

print "Created database efficiently"

So was that troublesome? I hope not. So lets proceed. The following factor is to make tables in our database. So how do you go about doing it? Simply observe me.

Making tables in a database

import sqlite3
 
conn = sqlite3.join("check.db")

<a href="http://freepythontips.wordpress.com/2013/07/28/the-with-statement/">with</a> conn:
    cursor = conn.cursor()
 
    # create a desk
    cursor.execute("""CREATE TABLE books
               (title textual content, creator textual content)""")

Within the above code we made a desk with the title of e book. It has the next fields: title and creator. Each of those fields have the info sort of textual content. Initially we made a database with the title of check.db and after that we made a cursor object which permits us to interface with our database and execute queries. So what now. Now we have created a database and made a desk. Now we’ve to insert some knowledge in our desk. Lets proceed.

Inserting knowledge to the database

# insert some knowledge
cursor.execute("INSERT INTO books VALUES ('Delight and Prejudice', 'Jane Austen')")
 
# save knowledge to database
conn.commit()
 
# insert a number of information utilizing the safer "?" methodology
books = [('Harry Potter', 'J.K Rowling'),
          ('The Lord of the Rings', 'J. R. R. Tolkien'),
          ('The Hobbit','J. R. R. Tolkien')]
cursor.executemany("INSERT INTO books VALUES (?,?)", books)
conn.commit()

So within the above code i confirmed you two methods to place some knowledge into the database. The primary methodology is to execute a single question and the second methodology is to execute a number of queries in the identical time. Within the second methodology we may have used the string substitution %s however it’s identified to be probably harmful and might result in SQL Injection. So whats left now? Eradicating and updating the info? No downside i’ll cowl that as nicely. Simply study the code beneath.

Updating knowledge within the database

import sqlite3
 
conn = sqlite3.join("check.db")

<a href="http://freepythontips.wordpress.com/2013/07/28/the-with-statement/">with</a> conn:
    cursor = conn.cursor()
 
    sql = """
        UPDATE books 
        SET creator="Yasoob" 
        WHERE creator="J.Ok Rowling"
    """
    cursor.execute(sql)

Within the above code we up to date our document by changing J.Ok Rowling with Yasoob. Check out the beneath code for deleting the information.

Deleting information from the database

import sqlite3
 
conn = sqlite3.join("check.db")

<a href="http://freepythontips.wordpress.com/2013/07/28/the-with-statement/">with</a> conn:
    cursor = conn.cursor()
 
    sql = """
        DELETE FROM books
        WHERE creator="Yasoob"
    """
    cursor.execute(sql)

Within the above code we deleted the document of these books whose author was ‘Yasoob’. Now i’m going to point out you easy methods to show knowledge from the desk. It’s straightforward. Just some strains of code.

Displaying knowledge from the database

import sqlite3

conn = sqlite3.join('check.db')
print "Opened database efficiently";

cursor = conn.execute("SELECT title, creator  from books")
for row in cursor:
   print "Title = ", row[0]
   print "Creator = ", row[1], "n"

print "Operation performed efficiently";
conn.shut()

Within the above code we opened our database file and displayed the information on the display. Are you aware that we may have additionally performed:

conn = sqlite3.join(':reminiscence:')

So what is that this code doing? The :reminiscence: is a particular title which creates the database within the ram and allows you to execute any question. Lastly I wish to inform you about sqlalchemy which is a database library for python and takes care of a variety of issues for you. It does all of the escaping for you so that you gained’t need to mess with the annoyances of changing embedded single quotes into one thing that SQLite will settle for. It’s part of my earlier weblog submit as nicely.

So now goodbye to you all. I hope you appreciated studying in the present day’s submit as a lot as i loved writing it. Keep tuned for my subsequent submit.

For additional studying i counsel the zetcode database tutorial.