James Gardner

November 25, 2004

© 2002-2004 James Gardner All rights reserved.


1 Abstract

SnakeSQL -- Pure Python SQL database supporting NULLs

SnakeSQL is a pure Python SQL database written to remove the dependence of the Python Web Modules on 3rd party drivers for non-Python databases like MySQL.

The database supports the simplest possible subset of ANSI SQL 92 including NULLs (something other pure Python databases such as Gadfly do not currently support).

The database is fully DB-API 2.0 compliant and is written in layers so that it can easily be extended to support different storage mechanisms. Currently implemented are a fast binary DBM driver (default) and a slower CSV file driver (handy for viewing table contents when designing and developing an application or database structure).

See Also:

Python DB-SIG Pages
To find out more about the DB-API 2.0 or how to program using DB-API 2.0 methods, please visit The rest of this documentation will assume you are not interested in using the cursor as a DB-API 2.0 cursor and that you want to know the additional features available.

2 Installation Options

Quick answer: python install

If you need more information than that, read on...

SnakeSQL doesn't actually need formally installing although it is easiest if you do install it. The only requirement is that either the SnakeSQL module folder is in a place where Python looks for modules or you explicitly tell Python where it is. It really is very easy but I've tried to explain it in full which is why there are so many options.

Install SnakeSQL

The easiest way to get the SnakeSQL module on your PYTHONPATH is to install the software to your Python Lib/site-packages directory where Python looks for new modules. You can only do this if you have appropriate permissions so some of the other options might be more suitable. Try should probably try this first though.

Windows Users: Double click and follow the instructions.

All others: Load up a shell. In the installation directory execute the command below and follow the instructions:

python install

Note: Bear in mind that Python on your system may be called python2 or python2.3 so replace python in the example above with whatever is appropriate for your platform.

Put SnakeSQL in your script directory

Place the SnakeSQL directory of the distribution in the same directory as the script which uses the modules. This works because Python always searches the directory containing a script when looking for modules.

Warning: Be careful to use the correct SnakeSQL directory. The one you need to copy is not the one containing the script, it is the SnakeSQL directory in the SnakeSQL directory which contains the script.

Modify your PYTHONPATH Environmental Variable

PYTHONPATH is an environmental variable which contains a list of places Python should look for modules. You can add the full path to the correct SnakeSQL directory (see warning above) to this environmental variable. For information on how to set environmental variables refer to your operating system's help.

Modify sys.path

sys.path is a list of places Python looks for modules. You can add code similar to the following in every file using the modules:

>>> import sys
>>> sys.path.insert(0,'C:/path/to/distribution/directory')
replacing C:/path/to/distribution/directory with the path to the directory containing the correct SnakeSQL directory.

Testing your Installation

To test your installation load up a command prompt and start Python (Windows XP users can load Python if it is installed by clicking Start->Run and typing python <enter>).

You should see something similar to the following:

Python 2.2.3 (#42, May 30 2003, 18:12:08) [MSC 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.

Enter the following line of code and press enter:

import SnakeSQL

If you get another empty prompt as shown above then the installation has worked. If you get an error message then Python could not find the SnakeSQL module.

>>> import SnakeSQL
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
ImportError: No module named SnakeSQL

3 Using the Interactive Prompt

The SnakeSQL distribution comes with an Interactive Prompt which allows you to enter SQL queries directly into a database and see the results as a table of data. The file is in the scripts directory of the distribution.

Load a command prompt and type the following:

> python -h

This will display all the options available to you for using the interactive prompt. Now create and connect to the test database by typing the following:

> python -c test

This will run the interactive prompt. The database we are using is called test. The -c option means automatically create the database if it doesn't already exist. You should see something similar to this:

SnakeSQL Interactive Prompt
Type SQL or "exit" to quit, "help", "copyright" or "license" for information.

It looks a bit like the Python prompt but this prompt only allows SQL queries.

Try selecting some information form the table testTable:

>>> SELECT * FROM testTable
Error: Table 'testTable' not found.

Unsurprisingly, this gives an error message since we haven't yet created a table. All the supported commands, including creating a table will be demonstrated in the section Learning SQL.

One last thing. To exit the Interactive Prompt type exit and press Enter:

>>> exit

C:\Documents and Settings\James\Desktop\SnakeSQL\scripts>

As you can see you are returned to the usual command prompt.

4 Using SnakeSQL In Python Code

Although the ability to use SnakeSQL from the interactive prompt is extremely useful for debugging or remotely administering databases, SnakeSQL is designed to be used from within Python code.

4.1 Connecting to a Database and Obtaining a Cursor

Connecting to a database is really very easy. First import SnakeSQL:

import SnakeSQL

To create a new database named test you would use the following code:

connection = SnakeSQL.connect(database='test', autoCreate=True)

This creates the directory test to store the database tables in the directory of the script.

The code below will connect to an existing database named test:

connection = SnakeSQL.connect(database='test')

If you wanted to store the database as CSV files rather than the faster and better tested binary DBM format you could create a new like this:

connection = SnakeSQL.connect(database='test', driver='csv', autoCreate=True)

Warning: You cannot create a database in one format and expect to be able to connect to it using a driver for a different format. There is currently no way of converting between formats. You can't make two connections to the same database either. Only one connection is allowed at once although SnakeSQL manages things so that if two different programs try to connect to the same database at once the second program will waits for the first to finish.

Below is a description of the full range of parameters:

connect( database,[driver='dbm',][autoCreate=False])
The database name to connect to. (Also the name of the directory containing the tables).
The driver to use to serialise the database information to disk. The default is the fast binary DBM driver. Alternatively you could use the CSV format by specifying driver='csv'.
Whether to automatically create a new database if one doesn't already exist. Can be True or False. If not specified the default is autoCreate=False.

Warning: You should not rely on the positions of the parameters in the constructor staying the same in future versions. All parameters should be referenced in the name=value way.

Once you have connected to the database you will need a Cursor object with which to manipulate the database. We can easily create a cursor by calling the connection's cursor() method.

cursor = connection.cursor()

Cursor stands for a "CURrent Set Of Results". Some databases benefit from separating certain functions into a connection object and a cursor object. SnakeSQL doesn't but does things in this way to be compliant with version 2.0 of the Python DB-API.

The next sections show you how to use the cursor.

4.2 Executing SQL

The execute() method is used to retrieve information from a database and looks like this:

cursor.execute("SELECT * FROM Test")


cursor.execute("INSERT INTO Test (dateColumn, numberColumn) VALUES ('2004-11-8', 4)")

SnakeSQL uses ? style parameter substitution. This means the execute() method can take a list of values to substitute for any unquoted ? symbols in the SQL string.

values = [,11,8), 4]
cursor.execute("INSERT INTO Test (dateColumn, numberColumn) VALUES (?, ?)", values)


   "UPDATE Test SET dateColumn=?, numberColumn=? WHERE stringColumn=?",
   [,11,8), 4, "where string"]

At first sight the parameter substitution doesn't seem to offer much of an advantage but in fact it is extremely useful because SnakeSQL will automatically convert the values to SQL for you so that you don't need to convert them yourself.

The module also supports executemany(). This method does the same as execute() except it executes once for each sequence in the values parameter. For example:

   "UPDATE Test SET dateColumn=?, numberColumn=? WHERE stringColumn=?",
       [,11,8), 4, "string1"],
       [,11,8), 5, "string2"],
       [,11,8), 6, "string3"],

Note: None of you queries will be saved permanently to the database until you call connection.commit() to commit your changes. See later.

4.3 Retrieving Results

Once you have executed a SELECT statement you will want to retrieve the results. This is done using the cursor.fetchall() method:

cursor.execute("SELECT * FROM Test")
results = cursor.fetchall()

The results variable will always contain a tuple of tuples of fields. If the query matched no rows, result will be ((),). If it matched one row it will be in the form ((col1, col2, col3, etc),). If it matched more than one it will be in the form ((col1, col2, col3, etc), (col1, col2, col3, etc), etc )

You can print the results like this:

for row in cursor.fetchall():
    print "New Row"
    for field in row:
        print field

The cursor.fetchall() method will return the same results until another SQL query is executed using cursor.execute().

Note: The cursor also supports cursor.fetchone() and cursor.fetchmany(size) methods but it is currently just as efficient to use cursor.fetchall()

4.4 Understanding Field Types

SnakeSQL is a typed database. This means the information you send to the database and the information retrieved from the database will be automatically converted to the correct formats so that the programmer can treat the values as normal Python objects.

SnakeSQL currently supports the following types: Bool, Integer, Long, Float, String, Text, Date, Datetime and Time. They are described in the "SQL Reference" section of this document.

The values you pass to the cursor.execute() method should be of the correct type for the field they are representing. The values returned by the cursor.fetchall() method will automatically be returned as the appropriate Python type.

For example, Bool fields should have the Python values True or False, Long fields should be a valid Python long etc.

There are some exceptions:

String fields should contain Python strings of 255 characters of less. Text fields should contain 24 bit strings less. For strings longer than this length you should consider saving the string in a file and saving the filename in the database instead.

Date, Datetime and Time fields take Python, datetime.datetime and datetime.time objects respectively.

Unfortunately Python 2.2 and below do not support the datetime module. However SnakeSQL uses a compatibility module that behaves closely enough for most purposes. Simply import SnakeSQL and then you can import the datetime module automatically. This is what it looks like at the Python prompt:

Python 2.2.3 (#42, May 30 2003, 18:12:08) [MSC 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import SnakeSQL
>>> import datetime
>>> print,11,24)

4.5 Transactions, Rollbacks and Committing Changes

SnakeSQL supports basic transactions. This means that you can make a number of changes to the database but if your program crashes your changes will not be saved so that the database is not left in an unstable state where you have updated some tables but not others.

Changes are only saved (or committed) to the database when you call the connection object's commit() method:


If you have made a mistake and want to lose all the changes you have made, you can rollback the database to its previous state using the connection object's rollback() method:


Finally, if you have finished using a connection you can close it using the connection object's close() method. This will also rollback the database to the time you last committed your changes so if you want to save your changes you should call commit() first.


Note: Please note that making these changes to the connection object will automatically affect all cursor objects of that connection as well since they all share the same connection object.

4.6 Multiple Users and Locks

As mentioned earlier only one user can be make changes to a database at once (otherwise one user could over-write another user's information without the first user knowing). However, SnakeSQL is designed to work in a multi-threaded web environment when requests to read the database can happen from any process at any time. It is quite likely that more than one process will try to access a single database at once. If this happens, rather than raising an error, SnakeSQL will make any subsequent users wait until the first user finishes.

In theory, one of the processes accessing the database could get stuck in an infinite loop and not release the lock on the database to allow other users to access it. After a period of 2 seconds, if the process with the current lock on the database doesn't access it, the lock will be released and another process can obtain a lock. The first process will itself have to wait to obtain a lock.

If after 10 seconds a process still hasn't obtained a lock on the database it will forcibly obtain a lock, rolling back the database to the state it was in before the first process started changing it (no data corruption). If the first process (which took far too long to make its changes) tries to access the database again it will not be able to and will raise an Error.

In this way the only time an error is raised when multiple processes try to access the database at once is when a process has waited for 10 seconds to obtain a lock and at least 4 database calls have been made in the time it has waited. This could only happen in the event that an extremely slow database gets a lock and keeps accessing the database to avoid losing its lock (bad programming) or your server is overloaded with too many processes (more than one every two seconds) accessing a database for two seconds or longer. Under this sort of load you would be better using MySQL or a faster server.

5 SQL Reference

This section teaches you the basics of SQL, what it is and how it works. There is an excellent introduction at on which this section is based.

5.1 What is SQL?

SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.

Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard and some of them implement different keywords in different ways. SnakeSQL try's to use the most standard SQL syntax.

See Also:

Python Web Modules
The Python Web Modules come with two database layers which hide the variations between different SQL implementations. The web.database module provides functions for building SQL statements in the correct way for the underlying database and the web.database.object module lets you treat an SQL database like a Python dictionary with no need to understand SQL at all. Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

Below is an example of a table called "Person":

| LastName | FirstName | Number | DateOfBirth |
| Smith    | John      | 10     | 1980-01-01  |
| Doe      | John      | 3      | 1981-12-25  |

The table above contains two records (one for each person) and four columns (LastName, FirstName, Address, and DateOfBirth). Queries

With SQL, we can query a database and have a result set returned.

A query looks like this:

SELECT LastName FROM Person

Gives a result set like this:

| LastName |
| Smith    |
| Doe      |

Note: Some database systems require a semicolon at the end of the SQL statement. SnakeSQL does not.

5.2 The SELECT Statement

The SELECT statement is used to select data from a table. The tabular result is stored in a result table (called the result-set).

SELECT column_name(s) FROM table_name Select Some Columns

To select the columns named LastName and FirstName, use a SELECT statement like this:

SELECT LastName, FirstName FROM Person

Table Person:

| LastName | FirstName | Number | DateOfBirth |
| Smith    | John      | 10     | 1980-01-01  |
| Doe      | John      | 3      | 1981-12-25  |

Result Set:

| LastName | FirstName |
| Smith    | John      |
| Doe      | John      |

The order of the columns in the result is the same as the order of the columns in the query. Select All Columns

To select all columns from the Person table, use a * symbol instead of column names, like this:


Result Set:

| LastName | FirstName | Number | DateOfBirth |
| Smith    | John      | 10     | 1980-01-01  |
| Doe      | John      | 3      | 1981-12-25  |
+----------+-----------+--------+-------------+ Testing the SELECT Statement

The best way to learn SQL is to experiment. If you haven't done already you should read the section on using the Interactive Prompt. This will enable you to try out all the examples coming up. If you have read that section go ahead and create a database named test:

> python -c test

You will also need to have some data to play with so at the prompt type the following information (it is usually easiest to copy and paste).

CREATE TABLE Person (LastName String required, FirstName string, Number Integer, DateOfBirth Date) INSERT INTO Person (LastName, FirstName, Number, DateOfBirth) VALUES ('Smith', 'John', 10, '1980-01-01') INSERT INTO Person (LastName, FirstName, Number, DateOfBirth) VALUES ('Doe','John', 3, '1981-12-25')

You should see the following:

SnakeSQL Interactive Prompt
Type SQL or "exit" to quit, "help", "copyright" or "license" for information.
>>> CREATE TABLE Person (LastName String required, FirstName string, Number Inte
ger, DateOfBirth Date)
Query OK, 0 rows affected (0.01 sec)

>>> INSERT INTO Person (LastName, FirstName, Number, DateOfBirth) VALUES ('Smith
', 'John', 10, '1980-01-01')
Query OK, 1 row affected (0.02 sec)

>>> INSERT INTO Person (LastName, FirstName, Number, DateOfBirth) VALUES ('Doe',
 'John', 3, '1981-12-25')
Query OK, 1 row affected (0.01 sec)


Note: There shouldn't be any carriage returns when you write the SQL at the prompt. The SQL will automatically wrap to the next line as shown in the examples.

The table will now be the same as in the above examples. You can test the select statement:

>>> select * from Person
| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |
2 rows in set (0.01 sec)


Feel free to experiment with the select statement.

5.3 The WHERE Clause

The WHERE clause is used to specify a selection criterion.

The syntax of the where clause is:

SELECT column FROM table WHERE column operator value

With the WHERE clause, the following operators can be used:

Operator    Description
--------    -----------
= 	        Equal
<> 	        Not equal
> 	        Greater than
< 	        Less than
>= 	        Greater than or equal
<= 	        Less than or equal

Note: In some versions of SQL the <> operator may be written as != but not in SnakeSQL. Note that the equals operator in SQL is = not == as it is in Python. Using the WHERE Clause

To select only the people whose last name are Smith, we add a WHERE clause to the SELECT statement:

SELECT * FROM Person WHERE LastName='Smith'

Person table:

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |

Result set:

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
+----------+-----------+--------+-------------+ Using Quotes

Note that we have used single quotes around the conditional values in the examples.

SQL uses single quotes around text values (some database systems will also accept double quotes, not SnakeSQL). Numeric values should not be enclosed in quotes.

For text values:

This is correct:

SELECT * FROM Person WHERE LastName='Smith'

This is wrong:

SELECT * FROM Person WHERE LastName=Smith

For numeric values:

This is correct:

SELECT * FROM Person WHERE Number>10

This is wrong:

SELECT * FROM Person WHERE Number>'10'

Note: Since it is so common for SQL implementations to ignore this rule SnakeSQL tries to guess when you have forgotten to put the quotes in and tries to remove them if you put them in by accident around numbers. It is much better to use the quoting correctly though as future versions of SnakeSQL may not be so forgiving.

Snake SQL does not currently support the LIKE or BETWEEN conditions.

5.4 Special Characters

We have seen how to insert a string like James, we use the SQL 'James' but what if we want to insert the string tail's? Because it has a ' character in it we can't simply do 'tail's' as the SQL parser won't know which ' ends the string. Instead we use 'tail''s'. Double single quotes ('') in SQL mean a ' character.

Other special character like

can be put in strings as normal:
end of one line\nstart of new line

5.5 The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.


INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....) Insert a New Row

This Person table:

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |

And this SQL statement:

INSERT INTO Person (LastName, FirstName, Number, DateOfBirth)
VALUES ('Blair', 'Tony', 8, '1953-05-06')

Note: SnakeSQL expects the SQL to all be on one line. The line break here is for formatting

Will give this result:

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |
| 'Blair'  | 'Tony'    | 8      | 1953-05-06  |

5.6 The UPDATE Statement

The UPDATE statement is used to modify the data in a table.


UPDATE table_name SET column_name = new_value WHERE column_name = some_value Update one Column in a Row

Person table

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |
| 'Blair'  | 'Tony'    | 8      | 1953-05-06  |

We want to add a change Tony Blair's first name to James:

UPDATE Person SET FirstName = 'James' WHERE LastName = 'Blair'

Person table

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 10     | 1980-01-01  |
| 'Doe'    | 'John'    | 3      | 1981-12-25  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+ Update several Columns in a Row

We want to change the number of everyone with a FirstName John and make their DateOfBirth all 1980-01-01:

UPDATE Person SET Number = 1, DateOfBirth = '1980-01-01' WHERE FirstName = 'John'
| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |

5.7 The DELETE Statement

The DELETE statement is used to delete rows in a table.


DELETE FROM table_name
WHERE column_name = some_value Delete a Row


| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |

"John Doe" is going to be deleted:

DELETE FROM Person WHERE LastName = 'Rasmussen'


| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+ Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure and attributes will be intact:

DELETE FROM table_name


| LastName | FirstName | Number | DateOfBirth |


The ORDER BY keyword is used to sort the result. Sort the Rows

The ORDER BY clause is used to sort the rows.


| Company     | OrderNumber |
| 'Asda'      | 5678        |
| 'Morrisons' | 1234        |
| 'Tesco'     | 2345        |
| 'Morrisons' | 7654        |

To display the companies in alphabetical order:

SELECT Company, OrderNumber FROM Orders ORDER BY Company


| Company     | OrderNumber |
| 'Asda'      | 5678        |
| 'Morrisons' | 1234        |
| 'Morrisons' | 7654        |
| 'Tesco'     | 2345        |


To display the companies in alphabetical order AND the order numbers in numerical order:

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber


| Company     | OrderNumber |
| 'Asda'      | 5678        |
| 'Morrisons' | 1234        |
| 'Morrisons' | 7654        |
| 'Tesco'     | 2345        |


To display the companies in reverse alphabetical order:

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC


| Company     | OrderNumber |
| 'Tesco'     | 2345        |
| 'Morrisons' | 1234        |
| 'Morrisons' | 7654        |
| 'Asda'      | 5678        |


To display the companies in alphabetical order AND the order numbers in reverse numerical order:

SELECT Company, OrderNumber FROM Orders ORDER BY Company ASC, OrderNumber DESC


| Company     | OrderNumber |
| 'Asda'      | 5678        |
| 'Morrisons' | 7654        |
| 'Morrisons' | 1234        |
| 'Tesco'     | 2345        |

5.9 AND & OR

AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.

Original Table (used in the examples)

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |


Use AND to display each person with the first name equal to "John", and the last name equal to "Smith":

SELECT * FROM Person WHERE FirstName='John' AND LastName='Smith'

Result Set

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |


Use OR to display each person with the first name equal to "James", or the last name equal to "Smith":

SELECT * FROM Person WHERE FirstName='James' OR LastName='Smith'

Result Set

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |


You can also combine AND and OR:

SELECT * FROM Person WHERE FirstName='James' AND LastName='Smith' OR LastName='Blair'

Result Set

| LastName | FirstName | Number | DateOfBirth |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |

Warning: SnakeSQL does not yet support parentheses in the WHERE clause. Please be careful in your combinations of ANDs and ORs. SnakeSQL interprets them the same why Python would.

5.10 NULL Values

An important feature of SnakeSQL is its ability to support NULL values. A field which contains a NULL value is simply a field where no value has been set or the value as been set to contain no value. This is quite different, for example, from a String field which has been set a value '', an empty string.

Original Table (used in the examples)

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |

Our query

UPDATE Person SET FirstName=NULL WHERE LastName='Doe'

Our table now looks like this:

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | NULL      | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |

This is quite different from this query which simply sets the FirstName to the string 'NULL' not the value NULL:

UPDATE Person SET FirstName='NULL' WHERE FirstName=NULL

Our table now looks like this:

| LastName | FirstName | Number | DateOfBirth |
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'NULL'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |

This is one of the reasons why it is important to use the correct quotations around values in you SQL.

Note: Technically speaking WHERE FirstName=NULL is incorrect SQL because a field cannot be equal to nothing. Instead, the correct syntax is WHERE FirstName is NULL but SnakeSQL doesn't currently only supports the =NULL version.

If you inserted a row into the table without specifying all the columns the columns you had not specified would contain the value NULL unless you had specified a DEFAULT value when you created the table.


To create a table in a database:


CREATE TABLE table_name
column_name1 data_type options,
column_name2 data_type options,


This example demonstrates how you can create a table named "Person", with four columns. The column names will be LastName, FirstName, Number, and DateOfBirth:

CREATE TABLE Person (LastName String, FirstName String, Number String, DateOfBirth Date)

The data_type specifies what type of data the column can hold. The table below contains the data types supported by SnakeSQL:

Data Type  Description 
Bool Either True or False
String A string field taking strings values up to 255 characters
Text A text field for storing large amounts of text (up to 16k characters)
Integer An integer field taking any integer that is a valid Python integer (but not long)
Long Any valid Python long number
Float Any Python floating point number
Date A date field. Takes values in the form of python datetime objects. Only stores days, months and years, any other information is truncated. Dates from 0001-01-01 to 9999-12-31.
Time A time field. Takes values in the form of python datetime objects. Only stores hours, minutes and seconds, any other information is truncated.
DateTime A datetime field. Takes values in the form of python datetime objects. Only stores days, months, years, hours, minutes and seconds, any other information is truncated.

The options can be used to further specify what values the field can take. They are described in the next sections. REQUIRED

In SnakeSQL, REQUIRED simply means that the field cannot contain a NULL value. If you insert a row into a table with a REQUIRED field, you must specify a value for the field unless you have also specified the field to have a DEFAULT value which isn't NULL in which case the default value will be used. If you try to set the field to NULL an error will be raised.

To create a table with LastName and FirstName columns where LastName could not take a NULL value you would use:

CREATE TABLE Person (LastName String REQUIRED, FirstName String) UNIQUE

In SnakeSQL, a UNIQUE field is one in which all values in the table must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is not specified as REQUIRED, i.e. it is allowed to contain NULL values, it can contain multiple NULL values.

A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL will declare them so implicitly (and silently). A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

To create a table with LastName and FirstName columns where all the values of LastName had to be different or NULL you would use:

CREATE TABLE Person (LastName String UNIQUE, FirstName String)

If a field is specified as UNIQUE, SnakeSQL will not also let you specify a DEFAULT value. PRIMARY KEY

A PRIMARY KEY in SnakeSQL is a field which cannot be NULL, must be UNIQUE and does not take a DEFAULT value. Each table can only have one field as a PRIMARY KEY.

Primary keys can be used by SnakeSQL's drivers to speed up database queries.

To create a table with LastName and FirstName columns where LastName is a primary key use:

CREATE TABLE Person (LastName String PRIMARY KEY, FirstName String) DEFAULT

The DEFAULT option is used to specify a default value for a field to be used if a value isn't specified when a new row is added to a table.

To create a table with LastName and FirstName columns where the default value for LastName is 'Smith' we would use:

CREATE TABLE Person (LastName String DEFAULT='Smith', FirstName String)

You cannot specify a DEFAULT if the column is a PRIMARY KEY or UNIQUE.

If no DEFAULT is specified the DEFAULT is NULL Combinations of Options

You can use more than one option for the same column. Below are all the options written out:

column_name1 data_type REQUIRED,
column_name1 data_type UNIQUE,
column_name1 data_type PRIMARY KEY,
column_name1 data_type DEFAULT='SomeValue',
column_name1 data_type REQUIRED UNIQUE,
column_name1 data_type REQUIRED DEFAULT='SomeValue',

You could also use the following but PRIMARY KEYS are automatically UNIQUE and REQUIRED:

column_name1 data_type REQUIRED PRIMARY KEY,
column_name1 data_type UNIQUE PRIMARY KEY,
column_name1 data_type REQUIRED UNIQUE PRIMARY KEY,

5.12 DROP Table Delete a Table

To delete a table (the table structure and attributes will also be deleted):

DROP TABLE table_name

6 Licence

SnakeSQL is released under the GNU GPL.

		       Version 2, June 1991

 Copyright (C) 1989, 1991 Free Software Foundation, Inc.
                       59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 Everyone is permitted to copy and distribute verbatim copies
 of this license document, but changing it is not allowed.


  The licenses for most software are designed to take away your
freedom to share and change it.  By contrast, the GNU General Public
License is intended to guarantee your freedom to share and change free
software--to make sure the software is free for all its users.  This
General Public License applies to most of the Free Software
Foundation's software and to any other program whose authors commit to
using it.  (Some other Free Software Foundation software is covered by
the GNU Library General Public License instead.)  You can apply it to
your programs, too.

  When we speak of free software, we are referring to freedom, not
price.  Our General Public Licenses are designed to make sure that you
have the freedom to distribute copies of free software (and charge for
this service if you wish), that you receive source code or can get it
if you want it, that you can change the software or use pieces of it
in new free programs; and that you know you can do these things.

  To protect your rights, we need to make restrictions that forbid
anyone to deny you these rights or to ask you to surrender the rights.
These restrictions translate to certain responsibilities for you if you
distribute copies of the software, or if you modify it.

  For example, if you distribute copies of such a program, whether
gratis or for a fee, you must give the recipients all the rights that
you have.  You must make sure that they, too, receive or can get the
source code.  And you must show them these terms so they know their

  We protect your rights with two steps: (1) copyright the software, and
(2) offer you this license which gives you legal permission to copy,
distribute and/or modify the software.

  Also, for each author's protection and ours, we want to make certain
that everyone understands that there is no warranty for this free
software.  If the software is modified by someone else and passed on, we
want its recipients to know that what they have is not the original, so
that any problems introduced by others will not reflect on the original
authors' reputations.

  Finally, any free program is threatened constantly by software
patents.  We wish to avoid the danger that redistributors of a free
program will individually obtain patent licenses, in effect making the
program proprietary.  To prevent this, we have made it clear that any
patent must be licensed for everyone's free use or not licensed at all.

  The precise terms and conditions for copying, distribution and
modification follow.


  0. This License applies to any program or other work which contains
a notice placed by the copyright holder saying it may be distributed
under the terms of this General Public License.  The "Program", below,
refers to any such program or work, and a "work based on the Program"
means either the Program or any derivative work under copyright law:
that is to say, a work containing the Program or a portion of it,
either verbatim or with modifications and/or translated into another
language.  (Hereinafter, translation is included without limitation in
the term "modification".)  Each licensee is addressed as "you".

Activities other than copying, distribution and modification are not
covered by this License; they are outside its scope.  The act of
running the Program is not restricted, and the output from the Program
is covered only if its contents constitute a work based on the
Program (independent of having been made by running the Program).
Whether that is true depends on what the Program does.

  1. You may copy and distribute verbatim copies of the Program's
source code as you receive it, in any medium, provided that you
conspicuously and appropriately publish on each copy an appropriate
copyright notice and disclaimer of warranty; keep intact all the
notices that refer to this License and to the absence of any warranty;
and give any other recipients of the Program a copy of this License
along with the Program.

You may charge a fee for the physical act of transferring a copy, and
you may at your option offer warranty protection in exchange for a fee.

  2. You may modify your copy or copies of the Program or any portion
of it, thus forming a work based on the Program, and copy and
distribute such modifications or work under the terms of Section 1
above, provided that you also meet all of these conditions:

    a) You must cause the modified files to carry prominent notices
    stating that you changed the files and the date of any change.

    b) You must cause any work that you distribute or publish, that in
    whole or in part contains or is derived from the Program or any
    part thereof, to be licensed as a whole at no charge to all third
    parties under the terms of this License.

    c) If the modified program normally reads commands interactively
    when run, you must cause it, when started running for such
    interactive use in the most ordinary way, to print or display an
    announcement including an appropriate copyright notice and a
    notice that there is no warranty (or else, saying that you provide
    a warranty) and that users may redistribute the program under
    these conditions, and telling the user how to view a copy of this
    License.  (Exception: if the Program itself is interactive but
    does not normally print such an announcement, your work based on
    the Program is not required to print an announcement.)

These requirements apply to the modified work as a whole.  If
identifiable sections of that work are not derived from the Program,
and can be reasonably considered independent and separate works in
themselves, then this License, and its terms, do not apply to those
sections when you distribute them as separate works.  But when you
distribute the same sections as part of a whole which is a work based
on the Program, the distribution of the whole must be on the terms of
this License, whose permissions for other licensees extend to the
entire whole, and thus to each and every part regardless of who wrote it.

Thus, it is not the intent of this section to claim rights or contest
your rights to work written entirely by you; rather, the intent is to
exercise the right to control the distribution of derivative or
collective works based on the Program.

In addition, mere aggregation of another work not based on the Program
with the Program (or with a work based on the Program) on a volume of
a storage or distribution medium does not bring the other work under
the scope of this License.

  3. You may copy and distribute the Program (or a work based on it,
under Section 2) in object code or executable form under the terms of
Sections 1 and 2 above provided that you also do one of the following:

    a) Accompany it with the complete corresponding machine-readable
    source code, which must be distributed under the terms of Sections
    1 and 2 above on a medium customarily used for software interchange; or,

    b) Accompany it with a written offer, valid for at least three
    years, to give any third party, for a charge no more than your
    cost of physically performing source distribution, a complete
    machine-readable copy of the corresponding source code, to be
    distributed under the terms of Sections 1 and 2 above on a medium
    customarily used for software interchange; or,

    c) Accompany it with the information you received as to the offer
    to distribute corresponding source code.  (This alternative is
    allowed only for noncommercial distribution and only if you
    received the program in object code or executable form with such
    an offer, in accord with Subsection b above.)

The source code for a work means the preferred form of the work for
making modifications to it.  For an executable work, complete source
code means all the source code for all modules it contains, plus any
associated interface definition files, plus the scripts used to
control compilation and installation of the executable.  However, as a
special exception, the source code distributed need not include
anything that is normally distributed (in either source or binary
form) with the major components (compiler, kernel, and so on) of the
operating system on which the executable runs, unless that component
itself accompanies the executable.

If distribution of executable or object code is made by offering
access to copy from a designated place, then offering equivalent
access to copy the source code from the same place counts as
distribution of the source code, even though third parties are not
compelled to copy the source along with the object code.

  4. You may not copy, modify, sublicense, or distribute the Program
except as expressly provided under this License.  Any attempt
otherwise to copy, modify, sublicense or distribute the Program is
void, and will automatically terminate your rights under this License.
However, parties who have received copies, or rights, from you under
this License will not have their licenses terminated so long as such
parties remain in full compliance.

  5. You are not required to accept this License, since you have not
signed it.  However, nothing else grants you permission to modify or
distribute the Program or its derivative works.  These actions are
prohibited by law if you do not accept this License.  Therefore, by
modifying or distributing the Program (or any work based on the
Program), you indicate your acceptance of this License to do so, and
all its terms and conditions for copying, distributing or modifying
the Program or works based on it.

  6. Each time you redistribute the Program (or any work based on the
Program), the recipient automatically receives a license from the
original licensor to copy, distribute or modify the Program subject to
these terms and conditions.  You may not impose any further
restrictions on the recipients' exercise of the rights granted herein.
You are not responsible for enforcing compliance by third parties to
this License.

  7. If, as a consequence of a court judgment or allegation of patent
infringement or for any other reason (not limited to patent issues),
conditions are imposed on you (whether by court order, agreement or
otherwise) that contradict the conditions of this License, they do not
excuse you from the conditions of this License.  If you cannot
distribute so as to satisfy simultaneously your obligations under this
License and any other pertinent obligations, then as a consequence you
may not distribute the Program at all.  For example, if a patent
license would not permit royalty-free redistribution of the Program by
all those who receive copies directly or indirectly through you, then
the only way you could satisfy both it and this License would be to
refrain entirely from distribution of the Program.

If any portion of this section is held invalid or unenforceable under
any particular circumstance, the balance of the section is intended to
apply and the section as a whole is intended to apply in other

It is not the purpose of this section to induce you to infringe any
patents or other property right claims or to contest validity of any
such claims; this section has the sole purpose of protecting the
integrity of the free software distribution system, which is
implemented by public license practices.  Many people have made
generous contributions to the wide range of software distributed
through that system in reliance on consistent application of that
system; it is up to the author/donor to decide if he or she is willing
to distribute software through any other system and a licensee cannot
impose that choice.

This section is intended to make thoroughly clear what is believed to
be a consequence of the rest of this License.

  8. If the distribution and/or use of the Program is restricted in
certain countries either by patents or by copyrighted interfaces, the
original copyright holder who places the Program under this License
may add an explicit geographical distribution limitation excluding
those countries, so that distribution is permitted only in or among
countries not thus excluded.  In such case, this License incorporates
the limitation as if written in the body of this License.

  9. The Free Software Foundation may publish revised and/or new versions
of the General Public License from time to time.  Such new versions will
be similar in spirit to the present version, but may differ in detail to
address new problems or concerns.

Each version is given a distinguishing version number.  If the Program
specifies a version number of this License which applies to it and "any
later version", you have the option of following the terms and conditions
either of that version or of any later version published by the Free
Software Foundation.  If the Program does not specify a version number of
this License, you may choose any version ever published by the Free Software

  10. If you wish to incorporate parts of the Program into other free
programs whose distribution conditions are different, write to the author
to ask for permission.  For software which is copyrighted by the Free
Software Foundation, write to the Free Software Foundation; we sometimes
make exceptions for this.  Our decision will be guided by the two goals
of preserving the free status of all derivatives of our free software and
of promoting the sharing and reuse of software generally.





	    How to Apply These Terms to Your New Programs

  If you develop a new program, and you want it to be of the greatest
possible use to the public, the best way to achieve this is to make it
free software which everyone can redistribute and change under these terms.

  To do so, attach the following notices to the program.  It is safest
to attach them to the start of each source file to most effectively
convey the exclusion of warranty; and each file should have at least
the "copyright" line and a pointer to where the full notice is found.

    <one line to give the program's name and a brief idea of what it does.>
    Copyright (C) <year>  <name of author>

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

Also add information on how to contact you by electronic and paper mail.

If the program is interactive, make it output a short notice like this
when it starts in an interactive mode:

    Gnomovision version 69, Copyright (C) year name of author
    Gnomovision comes with ABSOLUTELY NO WARRANTY; for details type `show w'.
    This is free software, and you are welcome to redistribute it
    under certain conditions; type `show c' for details.

The hypothetical commands `show w' and `show c' should show the appropriate
parts of the General Public License.  Of course, the commands you use may
be called something other than `show w' and `show c'; they could even be
mouse-clicks or menu items--whatever suits your program.

You should also get your employer (if you work as a programmer) or your
school, if any, to sign a "copyright disclaimer" for the program, if
necessary.  Here is a sample; alter the names:

  Yoyodyne, Inc., hereby disclaims all copyright interest in the program
  `Gnomovision' (which makes passes at compilers) written by James Hacker.

  <signature of Ty Coon>, 1 April 1989
  Ty Coon, President of Vice

This General Public License does not permit incorporating your program into
proprietary programs.  If your program is a subroutine library, you may
consider it more useful to permit linking proprietary applications with the
library.  If this is what you want to do, use the GNU Library General
Public License instead of this License.

7 Future Plans

About this document ...

SnakeSQL, November 18, 2004

This document was generated using the tools distributed with Python 2.3

Comments and Questions

General comments and questions regarding this document should be sent by email to If you find specific errors in this document, either in the content or the presentation, please report the bug by emailing

For any of these channels, please be sure not to send HTML email. Thanks.

The authour of the web modules can be contacted at

This documentation is copyright James Gardner 2003-2004 All Rights Reserved. Permission is granted to freely distribute this documentation in electronic form. Commercial printing and distribution is forbidden but you are welcome to print single copies for your own use.