Using databases and structured Query Language (SQL)

The code to create a database file and a table named Tracks with both columns in the database is as follows:

1 #!/usr/bin/python2 ImportSqlite33conn = Sqlite3.connect ('Music.sqlite3')4Cur =conn.cursor ()5Cur.execute ('DROP TABLE IF EXISTS Tracks')6Cur.execute ('CREATE TABLE Tracks (title Text,plays INTEGER)')7Conn.close ()

The connect operation makes a "connection" to the database stored in the ' file Music.sqlite3 in the ' current directory. If The file does not exist, it'll be created. The reason this is called a "connection" are that sometimes the database are stored on a separate "database server" from th E server on which we is running our application. In our simple examples the database would just is a local file in the same directory as the Python code we is running.
A cursor is like a file handle, we can use the perform operations on the data stored in the database. Calling cursor () is very similar conceptually to calling open () when dealing with text files.

Once we have the cursor, we can begin to execute commands on the contents of the database using the Execute () method.
The database language is called structured Query language or SQL for short.
Http://en.wikipedia.org/wiki/SQL
In our example, we is executing the SQL commands in our database. As a convention, we'll show the SQL keywords in uppercase and the parts of the command that we are adding (such as the T Able and column names) would be shown in lowercase.
The first SQL command removes the Tracks table from the database if it exists. This-pattern is simply-allow us-to-run the same program to create the Tracks table over and over again without causing An error. Note the DROP Table command deletes the table and all of their contents from the database (i.e, there is no "undo").
Cur.execute (' DROP TABLE IF EXISTS Tracks ')
The second command creates a table named Tracks with a text column named title and an integer column named plays.
Cur.execute (' CREATE TABLE Tracks (title TEXT, plays INTEGER) ')
Now there is created a table named Tracks, we can put some data into that table using the SQL INSERT operation. Again, we begin by making a connection to the database and obtaining the cursor. We can then execute SQL commands using the cursor.
The SQL INSERT command indicates which table we is using and then defines a new row by listing the fields we want to incl Ude (title, plays) followed by the VALUES of we want placed in the new row. We specify the values as question marks (?,?) to indicate, the actual values is passed in as a tuple (' My ', 15) As the second parameter to the Execute () call.

1 #!/usr/bin/python2 ImportSqlite33conn = Sqlite3.connect ('Music.sqlite3')4Cur =conn.cursor ()5Cur.execute ('INSERT into Tracks (title,plays) VALUES (?,?)',6('Thunderstruck', 20))7Cur.execute ('INSERT into Tracks (title,plays) VALUES (?,?)',8('My', 15))9 Conn.commit ()Ten Print  'Tracks:' OneCur.execute ('SELECT title, plays from Tracks') A  forRowinchcur: -     PrintRow -Cur.execute ('DELETE from Tracks WHERE plays <') the Conn.commit () -Cur.close ()

OUTPUT:

Tracks: (U ' Thunderstruck ', '(U'My", 15)

First we INSERT the table and use commit () to force the data to is written to the database file.

Then we use the SELECT command to retrieve the rows we just inserted from the table. On the SELECT command, we indicate which columns we would like (title, plays) and indicate which table we want to retrieve The data from. After we execute the SELECT statement, the cursor was something we can loop through in a for statement. For efficiency, the cursor does isn't read all of the data from the database when we execute the SELECT statement. Instead, the data is read on demand as we loop through the rows in the For statement.
Our For loop finds the first value as the title and the second value as the The Number of plays. Do is concerned that the title strings is shown starting with U '. This is a indication that strings be Unicode strings that's capable of storing non-latin character sets.
At the very end of the program, we execute a SQL command to DELETE the rows we had just created so we can run the Progra M over and over. The DELETE command shows the use of a WHERE clause this allows us to express a selection criterion so we can ask the Database to apply the command to only the rows that match the criterion. In this example the criterion happens to apply to all the rows so we empty the table off so we can run the program repeate dly. After Thedelete are performed, we also call commit () to force the data to being removed from the database.

Structured Query Language Summary
So far, we had been using the structured Query Language in our Python examples and has covered many of the basics of the SQL commands. In this section, we look at the SQL language in particular and give an overview of SQL syntax. Since there is so many different database vendors, the structured Query Language (SQL) is standardized so we could commu Nicate in a portable manner to the database systems from multiple vendors.
A relational database is made up of tables, rows, and columns. The columns generally has a type such as text, numeric, or date data. When we create a table, we indicate the names and types of the columns:
CREATE TABLE Tracks (title TEXT, plays INTEGER)
To insert a row into a table, we use the SQL Insert command:
INSERT into Tracks (title, plays) VALUES (' My ', ' )
The INSERT statement specifies the table name, then a list of the fields/columns so you would like to set in the new row , and then the keyword values and a list of corresponding values in the fields.
The SQL SELECT command is used to retrieve rows and columns from a database. The SELECT statement lets you specify which columns do would like to retrieve as well as a WHERE clause to SELECT which R oWS you would. It also allows an optional ORDER by clause to control the sorting of the returned rows.
SELECT * from Tracks WHERE title = ' My '
Using * Indicates, the want the database to return all of the columns for each row, that matches the WHERE clause.
Note, unlike in Python, in a SQL WHERE clause we use a single equal sign to indicate a test for equality rather than a dou ble equal sign. Other logical operations allowed in a WHERE clause include <,;, <=, >=,! =, as well as and and OR and Parenth ESEs to build your logical expressions.
You can request the returned rows is sorted by one of the fields as follows:
SELECT title,plays from Tracks ORDER by title
To remove a row, you need a WHERE clause in an SQL DELETE statement. The WHERE clause determines which rows is to be deleted:
DELETE from Tracks WHERE title = ' My '
It is possible to UPDATE a column or columns within one or more rows in a table using the SQL UPDATE statement as Foll OWS:
UPDATE Tracks SET plays = WHERE title = ' My '
The UPDATE statement specifies a table and then a list of fields and values to change after the SET keyword and then an op tional WHERE clause to select the rows that is to be updated. A single UPDATE statement would change all of the rows that match
The WHERE clause. If a WHERE clause is isn't specified, it performs the UPDATE on all of the rows in the table.
These four basic SQL commands(INSERT, SELECT, UPDATE, and DELETE)
Allow the four basic operations needed to create and maintain data.

Using databases and structured Query Language (SQL)