Connecting to Sqlite databases – Yasoob Khalid

Hello there fellas. Right this moment i’m going to show you use sqlite databases with python. This put up will cowl the fundamentals of constructing 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 larger then you should 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 under 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 connect with it as an alternative of constructing 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 information sort of textual content. To begin with 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. We’ve got created a database and made a desk. Now we’ve got to insert some information in our desk. Lets proceed.

Inserting information to the database

# insert some information
cursor.execute("INSERT INTO books VALUES ('Pleasure and Prejudice', 'Jane Austen')")
 
# save information to database
conn.commit()
 
# insert a number of data 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 information 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 recognized to be doubtlessly harmful and may result in SQL Injection. So whats left now? Eradicating and updating the information? No drawback i’ll cowl that as properly. Simply look at the code under.

Updating information 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 file by changing J.Ok Rowling with Yasoob. Check out the under code for deleting the data.

Deleting data 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 file of these books whose author was ‘Yasoob’. Now i’m going to indicate you show information from the desk. It’s simple. Just some traces of code.

Displaying information 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 "Writer = ", row[1], "n"

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

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

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 want to inform you about sqlalchemy which is a database library for python and takes care of lots of issues for you. It does all of the escaping for you so that you gained’t should mess with the annoyances of changing embedded single quotes into one thing that SQLite will settle for. It’s part of my earlier weblog put up as properly.

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

For additional studying i recommend the zetcode database tutorial.