 |
|
|
SnakeSQL | |
|
|
|
SnakeSQL | |
James Gardner
http://www.pythonweb.org/projects/snakesql/
docs at pythonweb.org
19 December 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.
The easiest way to get the SnakeSQL module directory listed on your PYTHONPATH environmental variable is to install the software to your Python Lib/site-packages directory where Python looks for new modules.
This method will only work if you have appropriate permissions. If you are using SnakeSQL on a hosted server for example, one of the other options might be more suitable. 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.
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.
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.
The sys.path variable is a list of places Python looks for modules. You can add the directory to SnakeSQL to your sys.path variable using code similar to the following in every file using the modules:
>>> import sys >>> sys.path.insert(0,'C:/path/to/distribution/directory')
Note:
Even on windows you can use the / character for paths. This saves you having to use escape characters to escape backslashes.
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 then pressing 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. You may get an error message like the one shown below:
>>> import SnakeSQL Traceback (most recent call last): File "<stdin>", line 1, in ? ImportError: No module named SnakeSQL >>>
If you see an ImportError it means that Python could not find the SnakeSQL module. You should go over the steps in this section again.
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 you can treat the values as normal Python objects.
SnakeSQL currently supports the following types which are described in the SQL Reference section of this document:
| Type | Description |
|---|---|
|
True or False |
|
Any Python integer (not Python Long or Decimal) |
|
Any Python long integer |
|
Any Python floating point number |
|
A string of 255 characters or less (XXX Not unicode?) |
|
A 24-bit string |
|
Any valid Python datetime.date object. Takes values in the form of python datetime objects. Only stores days, months and years, any other information is trunkated. Dates from 0001-01-01 to 9999-12-31. |
|
Any valid Python datetime.time object. Takes values in the form of python datetime objects. Only stores hours, minutes and seconds, any other information is trunkated. |
|
Any valid Python datetime.datetime object. Takes values in the form of python datetime objects. Only stores days, months, years, hours, minutes and seconds, any other information is trunkated. |
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 >>>
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.
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.
To see a list of all the available options for starting an Interactive Prompt session, load a command prompt and type the following at the command line:
> python snake.py -h
This will display all the options available to you for using the interactive prompt.
To create and connect to a database named test type the following at the command line:
> 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 SQL Reference.
If you are new to SQL you should read the SQL Reference and test the examples using the interactive prompt.
One last thing. To exit the Interactive Prompt type exit and press Enter:
>>> exit C:\Documents and Settings\James\Desktop\SnakeSQL\scripts>
You will be 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.
Note:
Parameter substitution can be done for any value which needs conversion. This includes default values in CREATE statements and values in INSERT and UPDATE statements or WHERE clauses. Parameter substitutions are not available for strings which do not need conversions such as table names, column names etc.
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"],
]
)
In SnakeSQL this is no more efficient than executing a number of normal cursor.execute() methods.
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 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.
This section describes how to deal with special characters in Python and SnakeSQL.
Within a Python string, certain sequences have special meaning. Each of these sequences begins with a backslash \, known as the escape character. The values (and different escape methods) allowed in string literals are described in the Python documentation at http://www.python.org/doc/current/ref/strings.html. This is a brief summary.
Python recognizes the following escape sequences:
\\ Backslash (\)
\' Single quote (')
\" Double quote (")
\a ASCII Bell (BEL)
\b ASCII Backspace (BS)
\f ASCII Formfeed (FF)
\n ASCII Linefeed (LF)
\N{name} Character named name in the Unicode database (Unicode only)
\r ASCII Carriage Return (CR)
\t ASCII Horizontal Tab (TAB)
\uxxxx Character with 16-bit hex value xxxx (Unicode only)
\Uxxxxxxxx Character with 32-bit hex value xxxxxxxx (Unicode only)
\v ASCII Vertical Tab (VT)
\ooo Character with octal value ooo
\xhh Character with hex value hh
\b is interpreted as a backspace, but \B is not.
You can use these characters in SQL exactly the same way as you would in Python. For example 'end of one line\nstart of new line' is a valid SQL string containing a line break in the middle and could be used like this:
cursor.execute("INSERT INTO table (columnOne) VALUES ('end of one line\nstart of new line')")
There is one important point to note about how Python (and hence SnakeSQL) deals with these escape characters. If a string contains a backslash \ but the character after the backslash is not a character which can be escaped then the single backslash is treated as a single backslash. If the character can be used in an escape sequence then the backslash is treated as an escape character and the character is escaped.
Note: All examples in this section are from the Python prompt not the SnakeSQL one.
For example:
Python 2.4 (#60, Nov 30 2004, 11:49:19) [MSC v.1310 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> print 'hello\%world' hello\%world >>> print 'hello\nworld' hello world >>>
If a string contains both escaped and non-escaped characters Python guesses which are backslashes and which are escape characters:
>>> print 'hello\nworld\%again' hello world\%again >>>
If a string contains a double backslash \\ it is always treated as an escaped backslash character and printed as \.
>>> print '\\%' \% >>> print '\%' \%
This means that the following expression is True:
>>> print '\\%' == '\%' True >>>
But the following is not:
>>> print '\\\\%' == '\\%' False >>>
When writing Python strings you have to be very careful how the backslash character is being used and then you will have no problems.
The Interactive Prompt obeys the same special character rules as Python and SQL described above. One point which could cause confusion is the way the Interactive Prompt displays strings. If strings can be easily displayed they are. Otherwise the repr() function is used on them to explicitly display all their escape characters. This means all genuine backslashes appear double quoted.
Python 2.4 (#60, Nov 30 2004, 11:49:19) [MSC v.1310 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> print repr('hello\nworld\%once\\more')
'hello\nworld\\%once\\more'
In SQL all strings must be properly quoted using single quotes. To insert a string like James into the database, 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.
The single quote character ' is the only character which needs special treatment in SQL all the others like \n behave exactly as they do in Python as described above.
For example:
cursor.execute("INSERT INTO table (columnOne) VALUES ('James''s')")
If you are using the advanced cursor methods like cursor.insert() or cursor.update() (described later) or parameter substitution (described earlier), the easiest way to deal with special characters is to do nothing with them at all. The methods will automatically handle the conversions for you.
For example:
cursor.insert(
table='table',
columns=['columnOne'],
values=["James's"],
)
or
cursor.execute("INSERT INTO table (columnOne) VALUES (?)", "James's")
If you want explicitly want to use the cursor methods like cursor.insert() or cursor.update() but with quoted SQL strings rather than having the conversions done automatically you can do so like this:
cursor.insert(
table='table',
columns=['columnOne'],
sqlValues=["'James''s'"],
)
This section teaches you all the supported SQL commands. There is an excellent SQL tutorial at http://www.w3schools.com/sql on which the specification for SnakeSQL 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 LIKE Pattern match (described later)
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'
The LIKE condition is used to specify a search for a pattern in a column.
SELECT column FROM table WHERE column LIKE pattern
A % sign can be used to define wildcards (missing letters in the pattern).
The following SQL statement will return people with first names that start with an 'O':
SELECT * FROM Person WHERE FirstName LIKE 'O%'
The following SQL statement will return people with first names that end with an 'a':
SELECT column FROM table WHERE FirstName LIKE '%a'
The following SQL statement will return people with first names that contain the pattern 'la':
SELECT column FROM table WHERE FirstName LIKE '%la%'
You can use as many % characters as you need in the pattern to match zero or more characters. If you need to have an actual % characters in the pattern you will need to escape it like this
.
%
The following SQL statement will return values that end with a % character.
SELECT column FROM table WHERE Percentage LIKE '%\%'
Snake SQL does not support the BETWEEN condition since the same thing can be achieved using comparison operators.
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 = 'Doe'
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 | +----------+-----------+--------+-------------+
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 | +----------+-----------+--------+-------------+
Example
You can also combine AND and OR use parentheses to form complex expressions:
SELECT * FROM Person WHERE (FirstName='James' AND LastName='Smith') OR LastName='Blair'
Result Set
+----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ | 'Blair' | 'James' | 8 | 1953-05-06 | +----------+-----------+--------+-------------+
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:
Some SQL parsers consider WHERE FirstName=NULL to be incorrect because a field cannot be equal to nothing. Instead, they use WHERE FirstName is NULL. This seems too pedantic so SnakeSQL only supports the WHERE FirstName=NULL syntax.
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
People table below, the LastName column is the primary key, meaning that no two rows can have the same LastName. The LastName distinguishes two persons even if they have the same name.
When you look at the example tables below, notice that:
LastName column is the primary key of the People table
House column is the primary key of the Houses table
Owner column in the House table is used to refer to the people in the People table. Owner is a foreign key field.
People +----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ | Smith | John | 10 | 1980-01-01 | +----------+-----------+--------+-------------+ | Doe | James | 3 | 1981-12-25 | +----------+-----------+--------+-------------+ Houses +-------+-------+ | House | Owner | +-------+-------+ | 1 | Smith | +-------+-------+ | 2 | Smith | +-------+-------+ | 3 | Doe | +-------+-------+
1 and 2. In order to keep the database consistent you would not want to remove Smith from the People table or drop the People table because the Houses table would still contain a reference to Smith. Similarly you wouldn't want to insert or update a value in the Owner column of the Houses table which didn't exist as a primary key for the People table.
By specifying the Owner column of the Houses table as a foregin key these constraints are enforced by SnakeSQL.
The SQL for the tables is below. Note:
The line breaks in the first CREATE statement are for formatting; SnakeSQL doesn't support line breaks in SQL.
CREATE TABLE People (
LastName String PRIMARY KEY, FirstName String,
Number Integer, DateOfBirth Date
)
CREATE TABLE Houses (House Integer, Owner String FOREIGN KEY=People)
FOREIGN KEY it cannot have any other options. The table specified as providing the foreign key must have a primary key. It is the primary key value which is used as a foreign key in the other table.
To delete a table (the table structure and attributes will also be deleted):
DROP TABLE table_name
DROP TABLE table1, table2, table3
CREATE statement section.
People +----------+-----------+--------+-------------+ | LastName | FirstName | Number | DateOfBirth | +----------+-----------+--------+-------------+ | Smith | John | 10 | 1980-01-01 | +----------+-----------+--------+-------------+ | Doe | James | 3 | 1981-12-25 | +----------+-----------+--------+-------------+ Houses +-------+-------+ | House | Owner | +-------+-------+ | 1 | Smith | +-------+-------+ | 2 | Smith | +-------+-------+ | 3 | Doe | +-------+-------+
SELECT Houses.House, People.FirstName, Houses.Owner FROM People, Houses WHERE People.LastName=Houses.Owner
+--------------+------------------+--------------+ | Houses.House | People.FirstName | Houses.Owner | +--------------+------------------+--------------+ | 1 | 'John' | 'Smith' | | 2 | 'John' | 'Smith' | | 3 | 'James' | 'Doe' | +--------------+------------------+--------------+
SELECT Houses.House, People.FirstName, Houses.Owner FROM People, Houses WHERE People.LastName=Houses.Owner and People.DateOfBirth<'1981-01-01'
+--------------+------------------+--------------+ | Houses.House | People.FirstName | Houses.Owner | +--------------+------------------+--------------+ | 1 | 'John' | 'Smith' | | 2 | 'John' | 'Smith' | +--------------+------------------+--------------+
As well as supporting the standard DB-API 2.0 SnakeSQL cursor objects also have a number of SQL methods to allow you to manipulate the database without using SQL strings. These methods build SQL strings which you can then execute.
For example, we can produce an SQL SELECT statement as follows:
>>> print cursor.select( ... tables=['tableName'], ... columns=[ ... 'columnOne', ... 'columnTwo', ... 'columnThree', ... ] ... ) ... SELECT columnOne, columnTwo, columnThree FROM tableName
At first sight this looks more complicated than using the methods described earlier. When you are writing SQL code in Python you often already have the variables in the form required by these cursor methods and so it is actually easier to use these methods than putting together the parts into a string.
For the values execute, format and These methods are used to build an SQL string and (optionally) to execute the result.
| tables, columns, [where=None, ][order=None, ][execute=None, ][format=None, ]) |
Build an SQL string acording to to the options specified and optionally execute the SQL and return the results in the format specified. No error checking on field names if the SQL string is only being built. Strict error checking is only performed when executing the code.
'tableName.columnName'
cursor.where(). If where is a string it is converted to the correct format.
cursor.order(). If order is a string it is converted to the correct format.
False the function returns the SQL string needed to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specifed in the cursor which by default is True
'dict' to return them as a tuple of dictionary objects, 'tuple' to return them as a tuple of tuples, 'object' to return them as a tuple of dtuple objects which can be treated as a tuple or a dictionary or 'text' to return tables designed to be displayed in a terminal 80 characters wide. If not specified takes the value specifed in the cursor which by default is 'tuple'
| table, columns, values, sqlValues, [execute]) |
False the function returns the SQL string to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specifed in the cursor which by default is True
| table, columns, values, sqlValues, [, where][, execute]) |
cursor.where(). If where is a string it is converted to the correct format.
False the function returns the SQL string to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specifed in the cursor which by default is True
| table, [where][, execute]) |
cursor.where(). If where is a string it is converted to the correct format.
False the function returns the SQL string to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specifed in the cursor which by default is True
| table, columns [, execute]) |
Create table with fields specified by fields. fields is a tuple of field tuples which can be obtained as follows:
columns = [
cursor.column( field options... ),
cursor.column( field options... ),
cursor.column( field options... ),
cursor.column( field options... ),
]
cursor.column()
False the function returns the SQL string to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specifed in the cursor which by default is True
| tables[, execute]) |
False the function returns the SQL string to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specifed in the cursor which by default is True
| name, type[, required=0][, unique=0][, key=0][, default=None]) |
nametype'Char', 'String', 'Text', 'Integer', 'Float', 'Date', 'Time', 'DateTime'
requiredTrue means the field cannot have NULL values.
uniqueTrue if the value must be unique. Two fields in the column cannot have the same value unless that value is NULL
keydefault
| where) |
WHERE clause suitable for use in the select(), update() and delete() methods of the cursor object.
WHERE clause. Note:
where should not include the string 'WHERE' at the begining.
| order) |
ORDER BY clause suitable for use in the select() method of the cursor object.
ORDER BY clause. Note:
order should not include the string 'ORDER BY' at the begining.
If you want to execute the SQL as the cursor methods provide a parameter execute. If you specify this as True the SQL will be executed as shown below:
>>> print cursor.select( ... tables=['tableName'], ... columns=[ ... 'columnOne', ... 'columnTwo', ... 'columnThree', ... ], ... execute=True ... ) ((1, 2, 3), (4, 5, 6),)
If you always want the cursor methods to execute you can specify execute=True in the cursor() method of the connection object as shown below:
cursor = connection.cursor(execute=True)
All the methods select(), insert(), update(), delete(), create(), alter() and drop() will automatically execute if you use the cursor in this mode. Also, using these methods is slightly faster since the SQL does not need to be parsed.
The full specification for the cursor functions can be found in the Module Reference section.
You can change the way the results are returned from the database by specifying the format parameter in the cursor object fetch() methods or the select() method.
format can be:
'tuple''dict''object'dtuple objects. Each dtuple can be treated either as a tuple or a dictionary or the field value can be accessed as an attribute of the object. Note:
Certain field names will not work as attributes of the dtuple object since the field name maybe the same as an attribute name of the dtuple object
'text'
The default is 'tuple' which is what fields are returned by a normal DB-API 2.0 database.
Creating a cursor as follows will set the default format for fetching results to be 'dict'.
cursor = connection.cursor(format='dict')
cursor objects have an attribute named .tables. This is a dictionary of table name keys, each containing a Table object. Each table object contains information about the columns in the table which can be used to get properties or to conversion methods for those columns.
Table objects store all the meta data there is to know about an SQL table. A simple Table class might look like this:
class Table:
def __init__(self, name, columns=[]):
self.name = name
self.columns = tuple(columns)
self.primaryKey = None
self.parentTables = []
self.childTables = []
def columnExists(self, columnName):
for column in self.columns:
if column.name == columnName:
return True
return False
def column(self, columnName):
for column in self.columns:
if column.name == columnName:
return column
raise DataError("Column %s not found."%(repr(columnName)))
Table objects must have the following two atrributes:
namecolumnsSELECT * FROM table statement
They should have the following methods:
column(name)columnExists(name)True if the table has a column named name, False otherwise
Table objects could also contain any other useful methods which the module implementer chooses.
Column objects store all the meta data there is to know about a particular column. A simple columm class would look like this:
class Column:
def __init__(self, table, name, type, required, unique, key, default, converter, position):
self.name = name
self.type = type
self.table = table
self.required = required
self.unique = unique
self.key = key
self.default = default
self.converter = converter
self.position = position
def get(self, columnName):
for column in self.columns:
if column.name == columnName:
return column
raise DataError("Column %s not found."%(repr(columnName)))
Attributes
nametypebaseTypetablerequiredTrue or False depending on whether the column value is required (ie cannot be NULL) or not
uniqueTrue if the field should be unique, False otherwise
keyTrue if the field is a primary key, False otherwise
defaultconverterpositionSELECT * FROM table
SnakeSQL is designed in a way to allow it to be easy to create custom field types or new drivers. This really needs a good few pages of explanation which I haven't had time to document yet. Please get in touch help@pythonweb.org if you plan to write such extensions.
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.
0.5.2 - Basic Joins - SQL parser supports multiple tables in SELECT and table names in WHERE clauses - connection.where() modified to act on multiple tables - DROP supports multiple tables 0.5.1 - Foreign key constraints - cannot update or insert a value into a child record for a parent which does not exist - cannot drop a parent table until all children are removed, can drop child table - DROP supports multiple tables 0.5.0 - Foreign key constraints - cannot delete a parent record if child records still link to it 0.4.3 - Redefined variables fields instead of columns - parameter substitution for create - values and sqlValues options working for insert, update - Document new features: cursor methods, escape characters, parenthesis, like 0.4.2 - Not using keywords or types as column names - Restuctures the SQLParser so output feeds directly into corresponding SQLBuilder functions - Integrate SQL Builder into cursor functions - sqlValues option for insert, update 0.4.1 - Where clause supporting parentheses - LIKE keyword - Completed SQLBuilder 0.4.0 - Re-wrote SQL Parser to return raw SQL - Re-wrote conversions to use the raw SQL - Wrote basic SQL Builder (not integrated into code yet) - distTools supports more formats 0.3.6 - Implemented fetchModes, debug, - Basic web.database cursor functions supported - Fixed rollback issues - Implemented new test example. 0.3.5 - Re-written to support Python 2.1
Check if DBM files can be moved cross platform easily. Add a DISTINCT keyword to SELECT DELETE * FROM syntax DROP Database syntax Brackets around WHERE clause statements Need to be fussy about is NULL rather than = NULL ? Need to write an ORDER BY test Need to write a WHERE test Need to write an incorrect values test Creating tables and then selecting * is producing them in the wrong order. Support for BINARY and UNKNOWN fields. Change UNIQUE and DEFAULT behaviour Check the accepted ranges of Date, Datetime and Time fields
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
|
|
|
SnakeSQL | |