Python | PythonScript HQ

Filed Under: Guides, Tutorials Tagged With: database programming, fetchall, join, Python, query, SQL, Sqlite

Python Database Programming: Part Six

April 20, 2015 by maximumdx Leave a Comment

In the previous article, we covered creating and accessing databases with Sqlite. In this article, we continue our look at the Python database APIs.

You must download a separate DB API module for each database you need to access. Modules exist for most major databases with the exception of Microsoft’s SQL Server. You can access SQL Server using an ODBC (Open Database Connectivity) module, though. In fact, the mxODBC module can communicate with most databases using ODBC on Windows or an OBDC bridge on UNIX or Linux.

A complete listing of databases is available at the official Python wiki. You just need to download the modules you need, and follow the instructions that come with the modules to install them. You may need a C compiler and build environment to install some of the database modules. If you do, it will be described in the module’s own documentation. For some databases, such as oracle, you can choose among a number of slightly different modules.

Python Database Programming: Creating a Connection Object

A Connection object provides the means to communicate from your script to a database program. Note the major assumption here that the database is running in a separate process (or processes). The Python database modules connect to the database. They do not include the database application itself.

Each database module needs to provide a connect function that returns a connection object. The parameters that are passed to connect vary by the module and what is required to communicate with the database.

Parameter	Usage
Dsn	Data source name. This usually includes the name of your database and the server where it’s running.
Host	Host, or network system name, on which the database runs.
Database	Name of the database.
User	User name for connecting to the database.
Password	Password for the given user name.

The above table summarizes some of the more common parameters. For example, here’s a typical connect statement:

conn = dbmodule.connect(dsn='localhost:MYDATABASE', user='chris', password='agenda')

With a Connection object, you can work with transactions, close the connection, and get a cursor.

A cursor is a Python object that enables you to work with the database. In database terms, the cursor is positioned at a particular location within a table or tables in the database, much like the cursor on your screen when you’re editing a document, which is positioned at a pixel location.

To get a cursor, you need to call the cursor method on the connection object:

cursor = conn.cursor()

Once you have a cursor, you can perform operations on the database, such as inserting records.

import os
import sqlite3

conn = sqlite3.connect('my_database')
cursor=conn.cursor()

# Create players
cursor.execute("""
insert into player(idnum,lastname,firstname,age,team,lefthanded,totalwar,earliestfreeagent)
values(100,"d'Arnaud",'Travis',26,18,'No',0.0,2020)""")

cursor.execute("""
insert into player(idnum,lastname,firstname,age,team,lefthanded,totalwar,earliestfreeagent)
values(101,'Duda','Lucas',29,18,'Yes',2.9,2018)""") 

cursor.execute("""
insert into player(idnum,lastname,firstname,age,team,lefthanded,totalwar,earliestfreeagent)
values(102,'Harper','Bryce',22,20,'Yes',9.6,2019)""")

# Create teams
cursor.execute("""
insert into team(teamid,teamname,teamballpark)
values(18,'New York Mets','Citi Field')""")

cursor.execute("""
insert into team(teamid,teamname,teamballpark)
values(20,'Washington Nationals','Nationals Park')""")

conn.commit()
cursor.close()
conn.close()

The first few lines of the script set up the database connection and create a cursor object:

import os
import sqlite3
conn = sqlite3.connect('my_database')
cursor=conn.cursor()

Note how we connect to an Sqlite database. To conect to a different database, replace this with your database-specific module, and modify the call to use the connect function from that database module, as needed.

The next several lines execute a number of SQL statements to insert rows into the two tables set up earlier: player and team. The execute method on the cursor object executes the SQL statement:

cursor.execute("""
insert into player(idnum,lastname,firstname,age,team,lefthanded,totalwar,earliestfreeagent)
values(100,"d'Arnaud",'Travis',26,18,'No',0.0,2020)""")

This example uses a triple-quoted string to cross a number of lines as needed. You will find that SQL commands, especially those embedded within Python scripts, are easier to understand if you can format the commands over a number of lines. This becomes helpful with more complex queries. Also, note that we used double quotes around the last name in this example (“d’Arnaud”) so we could include an apostrophe in the name (if we used single quotes, the apostrophe, identical to a single quote mark, would have denoted the end of the string).

To save your changes to the database, you must commit the transaction:

conn.commit()

Note that this method is called on the connection, not the cursor.

When you are done with the script, close the cursor and then the connection to free up resources. In short scripts like this, it may not seem important, but it helps the database program free its resources, as well as your Python script:

cursor.close()
conn.close()

You now have a small amount of sample data to work with while using other parts of the DB API.

External Links:

Filed Under: Guides, Tutorials Tagged With: API, database programming, ODBC, Oracle, Python, SQL, Sqlite

Python Database Programming: Part Five

April 13, 2015 by maximumdx Leave a Comment

Using Eclipse to create an Sqlite database.

In most cases when you are doing database programming, the database will be up and running. Your website hosting, for example, may have MySQL database access included. Your IT department may have standardized on a particular database, such as Oracle, DB/2, Sybase, or Informix.

Python Database Programming: Sqlite

But if you have no database yet, a good starting database is Sqlite. The main advantages of Sqlite are that it comes installed with Python and it is simple, yet functional. Using Sqlite is as simple as importing a module (sqlite3).

If you are working with another database, such as SQL Server, then the chances are good that a database has already been created. If not, follow the instructions from you database vendor.

import os
import sqlite3
conn = sqlite3.connect('my_database')
cursor=conn.cursor()
# Create tables
cursor.execute("""
create table player
    (idnum integer,
    lastname varchar,
    firstname varchar,
    age integer,
    team integer,
    lefthanded varchar,
    totalwar float(5,1),
    earliestfreeagent integer)
""")
cursor.execute("""
create table team
    (teamid integer,
    teamname varchar,
    teamballpark varchar)
""")
# Create indices
cursor.execute("""create index teamid on team (teamid)""")
cursor.execute("""create index idnum on player (idnum)""")
cursor.execute("""create index teamidfk on player(team)""")
conn.commit()
cursor.close()
conn.close()

This script should produce no output unless it raises an error. It uses the Sqlite API, which is somewhat similar to the DB API. After the import statements, the script creates a database connection with this statement:

conn = sqlite3.connect('my_database')

From there, the script gets a Cursor object. The Cursor object is used to create two tables (the Player and Team tables from the previous two articles) and define indexes on these tables.

The script then calls the commit method on the connection to save all the changes to disk. Sqlite stores all the data in the file my_database, which you should see in your Python directory.

Python’s support for relational databases started with ad hoc solutions, with one solution written to interface with each particular database, such as Oracle. Each database module created its own API, which was highly specific to that database because each database vendor evolved its own API based on its own needs. This can be difficult to support, because moving from one database to another requires that everything be rewritten and retested.

Over the years, Python has come to support a common database known as the DB API. Specific modules enable your Python scripts to communicate with different databases, such as DB/2, PostgreSQL, and so on. All of these modules support the common API, which makes your job a lot easier when you write scripts to access databases.

The DB API provides a minimal standard for working with databases, using Python structures and syntax wherever possible. This API includes the following:

Connections, which cover guidelines for how to connect to databases
Executing statements and stored procedures to query, update, insert and delete data with cursors
Transactions, with support for committing or rolling back a transaction
Examining metadata on the database module as well as on database and table structure
Defining the types of errors

In the next article, we will begin our look at the DB API.

External Links:

Filed Under: Guides, Tutorials Tagged With: API, database programming, Python, Sqlite

Python Database Programming: Part Four

April 6, 2015 by maximumdx Leave a Comment

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:

Operation	Usage
Select	Perform a query to search the database for specific data.
Update	Modify a row or rows, usually based on a certain condition.
Insert	Create new rows in the database.
Delete	Remove a row or rows from the database

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: