This article is part 2 of 2 in the series Python SQLite Tutorial

Last Updated: Thursday 12th December 2013

Following the SQLite3 series, this post is about some advanced topics when we are working with the SQLite3 module. If you missed the first part, you can find it here.

Using SQLite's date and datetime Types

Sometimes we need to insert and retrieve some date and datetime types in our SQLite3 database. When you execute the insert query with a date or datetime object, the sqlite3 module calls the default adapter and converts them to an ISO format. When you execute a query in order to retrieve those values, the sqlite3 module is going to return a string object:

The problem is that if you inserted a date object in the database, most of the time you are expecting a date object when you retrieve it, not a string object. This problem can be solved passing PARSE_DECLTYPES and PARSE_COLNAMES to the connect method:

Changing the connect method, the database now is returning a date object. The sqlite3 module uses the column's type to return the correct type of object. So, if we need to work with a datetime object, we must declare the column in the table as a timestamp type:

In case you have declared a column type as DATE, but you need to work with a datetime object, it is necessary to modify your query in order to parse the object correctly:

Using as "created_at [timestamp]" in the SQL query will make the adapter to parse the object correctly.

Insert Multiple Rows with SQLite's executemany

Sometimes we need to insert a sequence of objects in the database, the sqlite3 module provides the executemany method to execute a SQL query against a sequence.

Please note that each element of the sequence must be a tuple.

Execute SQL File with SQLite's executescript

The execute method only allows you to execute a single SQL sentence. If you need to execute several different SQL sentences you should use executescript method:

If you need to read the script from a file:

Please remember that it is a good idea to surround your code with a try/except/else clause in order to catch the exceptions. To learn more about the try/except/else keywords, checkout the Catching Python Exceptions – The try/except/else keywords article.

Defining SQLite SQL Functions

Sometimes we need to use our own functions in a statement, specially when we are inserting data in order to accomplish some specific task. A good example of this is when we are storing passwords in the database and we need to encrypt those passwords:

The create_function takes 3 parameters: name (the name used to call the function inside the statement), the number of parameters the function expects (1 parameter in this case) and a callable object (the function itself). To use our registered function, we called it using encrypt() in the statement.

Finally, PLEASE use a true encryption algorithm when you are storing passwords!

  • Slavik Timoschenko

    Thank you! You rock! 😀

  • Circle

    Nice write up!…Especially your last advice! Too few use it at all.

    However, I’d recommend using the term password hashing instead of encryption as the password is not really encrypted, even though cryptographic hashing algorithms are being used. Encryption implies that the procedure may be reversed. A hashed password would be very hard, if not impossible, to reconstruct from the hash alone.

  • Tonipé

    Thank you! Great job, really helpful.

  • Lord Rybec

    I want to reiterate what Circle said: You should never encrypt user passwords. Encryption is inherently reversible, which makes it easier for a hacker to crack passwords. You want a cryptographically secure hash (preferably with salt), which is not inherently reversible. Now days, SHA256 is about a minimum. I typically use SHA512, with salt and sometimes an additional key. If you are a bank (or have a lot of users, like Google or Facebook), you want something far more secure than even that.

    That aside, excellent article. I may be changing how some of my Python/SQLite web apps are organized in the near future!

    • Marco Scannadinari

      You should know that normal hash functions like sha256, and sha512 are vulnerable to rainbow table attacks.

      Since sha256(“abc”) will always produce “ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad”, an attacker can therefore apply this to all commonly used passwords, e.g.

          rainbow_table = {
        "ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad": "abc",  # <-- !!
        "5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8": "password",
        "03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4": "1234",
        # ...

      And simply query the hash that is found in the database to obtain the users password:

      jack_bauer_user = sql_exec('SELECT * FROM users WHERE username = "jack-bauer"')
      hashed_password = jack_bauer_user["hashed_password"]  # ba7816bf8... ( = sha256("abc"))
      cleartext_password = rainbow_table[hashed_password]  # "abc"
  • FrancisGan

    7874′), (‘Jack’, ‘5484522’);”’
    —> 10 c.executescript(script);
    12 # Print the results

    OperationalError: near “,”: syntax error

    sORRY, Could you help ? I have this error
    for the executescript

    • FrancisGan

      This is from copying your code from the above Using Python 2.7.6

  • Jaffels

    Awesome articles. Thank you