In the previous article, we introduced the concept of relational databases. In this article, we introduce the Structured Query Language, or SQL.

Python Database Programming: SQL

SQL (pronounced “sequel” or “S-Q-L”) defines a standard language for querying and modifying databases. SQL supports the following basic operations:

SQL offers more than these basic operations, but at least initially, these are the operations you will be using in writing Python applications: Query, Update, Insert and Delete (QUID), or Create, Read, Update and Delete (CRUD).

If you are not familiar with SQL, you’re going to want a good SQL book. O’Reilly has a useful book on SQL which can be downloaded here, and if you’re looking for a user-friendly SQL book, one is available here.

SQL is important because when you access databases with the Python DB API, you must first create SQL statements and then execute these statements by having the database evaluate them. Thus you will be using Python statements to execute SQL statements.

The basic SQL syntax for the operations mentioned above are:

SELECT columns FROM tables WHERE condition ORDER by columns ascending_or_descending

UPDATE table SET new values WHERE condition

INSERT INTO table (columns) VALUES (values)

DELETE FROM table WHERE condition

This covers the basic syntax, but there are many more optional parameters and specifiers available. You can use these options with Python’s DB API.

To insert a new row in the player table from the previous article, you can use an SQL query like this one (even though we are adding data and not getting data, the convention is that all SQL commands or statements are called queries):

insert into player (idnum, lastname, firstname, age, team, left-handed, totalwar, earliestfreeagent) values (103, ‘Murphy’, ‘Daniel’, 30, 18, ‘Yes’, 10.9, 2016)

In this example, the first tuple holds the names of the columns in the order you are using for inserting your data. The second tuple, after the keyword values, holds the data in the same order. Notice how SQL uses single quotes to delimit strings, and no quotes around numbers.

With queries, you can use wildcards such as * to say that you want an operation to be performed using all of the columns in a table. For example, to query all of the rows in the team table, showing all of the columns for each row, you can use a query like this:

select * from team

Note that SQL is not case-sensitive for its keywords, such as SELECT and FROM. But some databases require table and column names to be all uppercase. It is thus common to see people use SELECT and FROM and other operations in all capital letters to make them more easily distinguished from other parts of the query.

This SQL statement omits the names of the columns to read and any conditions that would otherwise narrow down the data that would be returned. Thus the query will return all of the columns (from the *) and all of the rows (because there is no where clause).

You can also perform a join with the select command, to query data from more than one table, but present it all in a single response. For example, to extract the team name with each player, you could perform a query like the following:

select player.firstname, player.lastname, team.name from player, team
where player.team = team.teamid order by lastname desc

In this example, the select statement requests two columns from the player table (the firstname and the lastname, but these are specified as coming from player by the convention of specifying the table name and the column name in the table) and one from the team table (team.name). The order by section of the statement tells the database to order the results by the value in the lastname column, in descending order.

To simplify these queries, you can use aliases for the table names, which make them easier to type and read. For example, to use the alias p with the player table, you can start a query like this:

select p.firstname, p.lastname from player p

In this case, you must place the alias, p, after the table name in the from clause. You can also use the following format with the optional keyword as, which could be easier for you to read:

select p.firstname, p.lastname from player as p

To modify or update a row, use a SQL statement like the following:

update player set age=27 where idnum=100

This example modifies the player with an idnum of 100 by setting that player’s age to 27. As with other queries, numbers do not need to have quotes around them; however, strings would need to be quoted with single quotes.

To delete a row, use an SQL statement like the following:

delete player where idnum=101

This example deletes the player with an idnum of 101, but doesn’t affect anything else in the database.

External Links:

Database Programming at wiki.python.org

Database Programming at python.about.com

Databases at docs.python-guide.org