James Gardner
http://www.pythonweb.org/projects/snakesql/
docs at pythonweb.org
November 25, 2004
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:
Quick answer: python setup.py 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 setup.py and follow the instructions.
All others: Load up a shell. In the installation directory execute the command below and follow the instructions:
python setup.py 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 setup.py script, it is the SnakeSQL directory in the SnakeSQL directory which contains the setup.py 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')
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 >>>
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 snake.py file is in the scripts directory of the distribution.
Load a command prompt and type the following:
> python snake.py -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 snake.py -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.
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.
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:
database,[driver='dbm',][autoCreate=False]) |
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
.
The execute() method is used to retrieve information from a database and looks like this:
cursor.execute("SELECT * FROM Test")
or
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 = [datetime.date(2004,11,8), 4] cursor.execute("INSERT INTO Test (dateColumn, numberColumn) VALUES (?, ?)", values)
or
cursor.execute( "UPDATE Test SET dateColumn=?, numberColumn=? WHERE stringColumn=?", [datetime.date(2004,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:
cursor.executemany( "UPDATE Test SET dateColumn=?, numberColumn=? WHERE stringColumn=?", [ [datetime.date(2004,11,8), 4, "string1"], [datetime.date(2004,11,8), 5, "string2"], [datetime.date(2004,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.
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()
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.date
, 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 datetime.date(2004,11,24) 2004-11-24 >>>
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:
connection.commit()
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:
connection.rollback()
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.
connection.commit() connection.close()
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.
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.
This section teaches you the basics of SQL, what it is and how it works. There is an excellent introduction at http://www.w3schools.com/sql on which this section is based.
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:
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).
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.
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
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.
To select all columns from the Person
table, use a *
symbol instead of column names, like this:
SELECT * FROM Person
Result Set:
+----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ | Smith | John | 10 | 1980-01-01 | +----------+-----------+--------+-------------+ | Doe | John | 3 | 1981-12-25 | +----------+-----------+--------+-------------+
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 snake.py -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.
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.
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 | +----------+-----------+--------+-------------+
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.
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
\n
end of one line\nstart of new line
The INSERT INTO statement is used to insert new rows into a table.
Syntax
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)
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 | +----------+-----------+--------+-------------+
The UPDATE statement is used to modify the data in a table.
Syntax:
UPDATE table_name SET column_name = new_value WHERE column_name = some_value
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 | +----------+-----------+--------+-------------+
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 | +----------+-----------+--------+-------------+
The DELETE statement is used to delete rows in a table.
Syntax
DELETE FROM table_name WHERE column_name = some_value
Person:
+----------+-----------+--------+-------------+ | 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'
Result
+----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ | 'Smith' | 'John' | 1 | 1980-01-01 | | 'Blair' | 'James' | 8 | 1953-05-06 | +----------+-----------+--------+-------------+
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
Result
+----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ +----------+-----------+--------+-------------+
The ORDER BY keyword is used to sort the result.
The ORDER BY clause is used to sort the rows.
Orders:
+-------------+-------------+ | 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
Result:
+-------------+-------------+ | Company | OrderNumber | +-------------+-------------+ | 'Asda' | 5678 | | 'Morrisons' | 1234 | | 'Morrisons' | 7654 | | 'Tesco' | 2345 | +-------------+-------------+
Example
To display the companies in alphabetical order AND the order numbers in numerical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
Result:
+-------------+-------------+ | Company | OrderNumber | +-------------+-------------+ | 'Asda' | 5678 | | 'Morrisons' | 1234 | | 'Morrisons' | 7654 | | 'Tesco' | 2345 | +-------------+-------------+
Example
To display the companies in reverse alphabetical order:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
Result:
+-------------+-------------+ | Company | OrderNumber | +-------------+-------------+ | 'Tesco' | 2345 | | 'Morrisons' | 1234 | | 'Morrisons' | 7654 | | 'Asda' | 5678 | +-------------+-------------+
Example
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
Result:
+-------------+-------------+ | Company | OrderNumber | +-------------+-------------+ | 'Asda' | 5678 | | 'Morrisons' | 7654 | | 'Morrisons' | 1234 | | 'Tesco' | 2345 | +-------------+-------------+
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 | +----------+-----------+--------+-------------+
Example
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 | +----------+-----------+--------+-------------+
Example
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 | +----------+-----------+--------+-------------+
Example
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.
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:
Syntax
CREATE TABLE table_name ( column_name1 data_type options, column_name2 data_type options, ....... )
Example
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 |
---|---|
|
Either True or False |
|
A string field taking strings values up to 255 characters |
|
A text field for storing large amounts of text (up to 16k characters) |
|
An integer field taking any integer that is a valid Python integer (but not long) |
|
Any valid Python long number |
|
Any Python floating point number |
|
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 . |
|
A time field. Takes values in the form of python datetime objects. Only stores hours, minutes and seconds, any other information is truncated. |
|
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.
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)
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.
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)
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
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,
To delete a table (the table structure and attributes will also be deleted):
DROP TABLE table_name
SnakeSQL is released under the GNU GPL.
GNU GENERAL PUBLIC LICENSE 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. Preamble 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 rights. 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. GNU GENERAL PUBLIC LICENSE TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION 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 circumstances. 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 Foundation. 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. NO WARRANTY 11. BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION. 12. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR REDISTRIBUTE THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. END OF TERMS AND CONDITIONS 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 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 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.
This document was generated using the tools distributed with Python 2.3
General comments and questions regarding this document should be sent by email to docs@pythonweb.org. If you find specific errors in this document, either in the content or the presentation, please report the bug by emailing bugs@pythonweb.org.
For any of these channels, please be sure not to send HTML email. Thanks.
The authour of the web modules can be contacted at mail@pythonweb.org
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.