pg8000 v1.00 — a new PostgreSQL/Python interface

pg8000 is a Pure-Python interface to the PostgreSQL database engine. Yesterday, it was released to the public for the first time.

pg8000’s name comes from the belief that it is probably about the 8000th PostgreSQL interface for Python. However, pg8000 is somewhat distinctive in that it is written entirely in Python and does not rely on any external libraries (such as a compiled python module, or PostgreSQL’s libpq library). As such, it is quite small and easy to deploy. It is suitable for distribution where one might not have a compiled libpq available, and it is a great alternative to supplying one with your package.

Why use pg8000?

  • No external dependencies other than Python’s standard library.
  • Pretty cool to hack on, since it is 100% Python with no C involved.
  • Being entirely written in Python means it should work with Jython, PyPy, or IronPython without too much difficulty.
  • libpq reads the entire result set into memory immediately following a query. pg8000 uses cursors to read chunks of rows into memory, attempting to find a balance between speed and memory usage for large datasets. You could accomplish this yourself using libpq by declaring cursors and then executing them to read rows, but this has two disadvantages:
    • You have to do it yourself.
    • You have to know when your query returns rows, because you can’t DECLARE CURSOR on an INSERT, UPDATE, DELETE, CREATE, ALTER, ect.
  • pg8000 offers objects to represent prepared statements. This makes them easy to use, which should increase their usage and improve your application’s performance.
  • It has some pretty nice documentation, I think.

Now, that being said, reality kicks in. Here’s why not to use pg8000:

  • It’s pretty new. This means there are likely bugs that haven’t been found yet. It will mature over the next couple weeks with some community feedback and some internal testing.
  • It doesn’t support the DB-API interface. I didn’t want to limit myself to DB-API, so I created just a slightly different interface that made more sense to me. I intend to include a DB-API wrapper in the next release, v1.01.
  • It isn’t thread-safe. When a sequence of messages needs to be sent to the PG backend, it often needs to occur in a given order. The next release, v1.01, will address this by protecting critical areas of the code.
  • It doesn’t support every PostgreSQL type, or even the majority of them. Notably lacking are: parameter send for float, datetime, decimal, interval; data receive for interval. This will just be a matter of time as well, and hopefully some user patches to add more functions. For the case of interval, I expect to optionally link in mxDateTime, but have a reasonable fallback if it is not available.
  • It doesn’t support UNIX sockets for connection to the PostgreSQL backend. I just don’t quite know how to reliably find the socket location. It seems that information is compiled into libpq. Support could be added very easily if it was just assumed that the socket location was provided by the user.
  • It only supports authentication to the PG backend via trust, ident, or md5 hashed password.

pg8000’s website is http://pybrary.net/pg8000/. The source code is directly accessible through SVN at http://svn.pybrary.net/pg8000/.

pyPdf 1.8 – with PDF encryption!

PyPdf version 1.8 has been released. This new version features two major improvements over the last release. The first is support for the PDF standard security handler, allowing the encryption and decryption of average PDF files. The second major feature is documentation.

The security handler was a fun project to implement. Sometimes, reading encryption algorithms in a document can be a fairly mind-warping experience. It’s not until you start to code the algorithm that you begin to understand the purpose, and how it all fits together. To be honest, sometimes even after you code it, it doesn’t make much sense.

I’m no cryptography expert, but I do feel I have a pretty good basic grasp of the technology and concepts. The PDF reference manual, section 3.5.2, contains a small number of algorithms that include processes like this:

Do the following 50 times: Take the output from the previous MD5 hash and pass the first n bytes of the output as input into a new MD5 hash…

Frankly, it doesn’t make much sense to me. It seems like busy-work. If the chosen hash function is believed to be secure, then rehashing the output 50 times is unnecessary. If the hash function turns out to be insecure, you should replace it, rather than running it 50 times. But I suppose it doesn’t matter much — pyPdf supports it now, whether it makes sense or not.

Documentation was another fun matter. It took a surprising amount of searching to find pythondoc, a documentation system. All I wanted was something that allowed the documentation to be integrated with the code, and allow hyperlinks between documentation bits. I recommend pythondoc if anyone has similar needs — it worked great to generate pyPdf’s documentation.

Pybrary.Plot

I just finished building a webpage for this software release, so I thought I’d mention it here.

Pybrary.Plot is a C# / .NET library designed for simple X/Y graphs and plots. It has the following capabilities:

  • multiple datasets with independent line and symbol styling,
  • numeric, date based, and time span based X axis options,
  • unlimited number of numeric Y axes,
  • scatter series type (arbitrary x/y values),
  • stacked plot series type,
  • basic menu driven user-interface (zooming, saving plot),
  • ability to save plot to clipboard as data and image,
  • capable of drawing on any graphics implementation – i.e. printers, screens,
  • Open Source, available under the modified BSD license.

Check out the website for more information, including screenshots and an online demo.

Beyond SELECT — Part 1: Constraints

You’ve just built your first database application. You’re proud of your accomplishments — and you should be. You mastered the fundamentals of SQL: creating a table, putting data into it, and querying it. You took the basic approach: when it came time to sum a column of numbers, you wrote a FOR loop. Everything works, but shouldn’t your database be doing more for you?

Many people are in the position just described. When time came to use a database, the first web hit for “SQL Tutorial” became the bible. SELECT, INSERT, and DELETE — the bread and butter of data manipulation. The SQL database has satisfied the need for data storage. What else can it do?

It can do a lot more. From aggregate functions to stored procedures, this article will help your database sing and dance. (Note: the author is not responsible for damage, physical, mental or emotional, caused by database servers and software singing and dancing)

This article is part 1 of a multi-part article, Beyond SELECT. This introduction to more than basic SQL is written with multiple databases in mind. Feature availability is documented for PostgreSQL, and MySQL. If you have information on the use of features here with other databases, please leave a comment and I will be glad to update this article. This is part 1 of a multi-part article. Links to the next parts will be added at the end as they are published. I am eager to do similar writing on a freelance basis for any publications. If you are interested, please leave a comment.

Constraints — Make a smarter database

Keys, and Primary Keys

A key is a value that uniquely identifies a row in a table. (PostgreSQL, MySQL 5) A table can have many keys, and most tables have at least one. One key is called the primary key, and is intended to be the method of identifying a row in a table.

Most new SQL users are familiar with the concept of a primary key in the form of an auto-incrementing integer column. In MySQL, this type of column is called AUTO_INCREMENT. In PostgreSQL, the SERIAL identifier is the equivilant. These data types are easy to use and understand, and make excellent primary keys in many cases.

In cases where integer primary keys are used, undesirable data duplication can be posible. For example, let’s create a simple table describing an employee, and recording his name and social security number:

CREATE TABLE employee (
    employee_id SERIAL,
    name VARCHAR(200),
    ssn VARCHAR(11)
);

With this data definition, a simple data entry mistake could create two employee rows representing the same employee. Maybe the HR director has big thick fingers, and he hit the enter button twice when creating a record. You could put application logic in place to prevent two people with the same social security numbers from being created, but your database can do that for you. Let’s drop the employee_id row, and use the SSN as the table’s primary key instead:

CREATE TABLE employee (
    ssn VARCHAR(11)
        PRIMARY KEY,
    name VARCHAR(200),
);

By taking this simple step, we’ve reduced the size of our employee table and also made it more “error-proof”. If you attempt to enter two employees with the same SSN, the database will refuse and give you an error.

Not every table will have a single field primary key. For example, a posting to an online forum can’t be keyed off the time, since multiple people could post at the same time. Sometimes it is easier to use an integer primary key, and sometimes it is a good idea to use a composite primary key. A composite primary key takes two columns, and ensures that no other row in the table exists with those same two columns. It uniquely identifies a row based upon multiple values.

Our company has just gone multinational. It seems unlikely, but somehow John Q in Canada has the same social insurance number as Jane B in Tennessee. Now we need to alter our database to allow both users in, but we still don’t want to go to an integer primary key. We can use a composite primary key on the country of employment AND the social (insurance | security) number of the employee:

CREATE TABLE employee (
    ssn VARCHAR(11),
    country VARCHAR(2),
    name VARCHAR(200),
    PRIMARY KEY (country, ssn)
);

Now (‘US’, ‘223-0423-85’, ‘Jane B’) is a distinct row from (‘CA’, ‘223-0423-85’, ‘John Q’), and both can be entered into the database. Of course, this is a contrived example since Canadian SIN numbers and US SSN numbers have different formats, but that doesn’t really matter.

What is the difference between a primary key and a key? Is there such a thing as a secondary key? A tertiary key? As it happens, there’s nothing much “primary” about a primary key. More than anything else, the “primary” is documentation, letting people know how this table was intended to be used. You can create any number of keys on your table by creating unique indicies.

For example, let’s say that you decided not to use a composite primary key for the employee table. Many other tables reference employee, so it is easiest to just use the original employee_id that we started with. However, we still don’t want multiple employees to have the same social security number, so we create a unique index on that column:

CREATE TABLE employee (
    employee_id SERIAL
        PRIMARY KEY,
    name VARCHAR(200),
    ssn VARCHAR(11)
);
CREATE UNIQUE INDEX employee_ssn_key ON employee (ssn);

Now only one row can have any given social security number, and only one row can have any given employee_id. This table has two equally valid keys. Keys should be created on any unique data, and ideally the primary key should not be a manufactured arbitrary number. However, you will find that integer primary keys can reduce the size and complexity of your database once you start using many foreign keys.

Foreign Key Constraints

A foreign key occurs when a row in one table references a row in another table. (PostgreSQL, MySQL 5) This is the relational part of a relational database system, and it is very common. Most people learn how to make two tables reference each other, but a surprising number don’t know that the database itself can help enforce that. Let’s create a little example:

CREATE TABLE manager (
    manager_id SERIAL
        PRIMARY KEY,
    name VARCHAR(200),
    evil BOOLEAN
);
CREATE TABLE employee (
    employee_id SERIAL
        PRIMARY KEY,
    name VARCHAR(200),
    manager_id INTEGER
);

In this scenario, each employee has a manager, which can be looked up based upon their manager_id. It’s actually a pretty bad design, for a couple different reasons. Most importantly, I could enter an employee with a manager_id that doesn’t exist. That could be intentional, or it could be a giant mistake. We’re going to introduce a foreign key constraint that will make sure all employees have a manager that exists:

CREATE TABLE employee (
    employee_id SERIAL
        PRIMARY KEY,
    name VARCHAR(200),
    manager_id INTEGER
        REFERENCES manager (manager_id)
);

Suddenly it is impossible to enter a manager_id that does not exist. The database is doing the hard work of checking every input manager_id for us, and all it took was a couple of words! But we actually didn’t quite accomplish what we want. The manager_id can still have a NULL value entered into it. Is there some kind of constraint that can fix that?

NULL / NOT NULL Constraints

The NOT NULL constraint is quite possibly the simplest we’re going to take a look at. (PostgreSQL, MySQL 5) Let’s throw the words NOT NULL into a table, and see what affect that has:

CREATE TABLE employee (
    employee_id SERIAL
        PRIMARY KEY,
    name VARCHAR(200)
        NOT NULL,
    manager_id INTEGER
        REFERENCES manager (manager_id)
        NOT NULL
);

Adding NOT NULL has made it so that the name and manager_id field must be provided. The employee_id field is already NOT NULL, because it is a database primary key. Now we have employees that must have names, and must have managers, and their managers must exist in the manager table.

There is a constraint (or, really, a lack of constraint) that is opposite of NOT NULL. Writing "NULL" in after a field allows a value to be NULL. This is actually the default for all columns, and it does not have to be explicitly stated. Personally, I like writing NULL for every field that can be NULL — it’s a reminder to myself, when I’m looking at the schema in the future.

Don’t you think it’s about time our employees got paid?

CHECK Constraints

A check constraint enforces a defined rule on a table or column. (PostgreSQL, unsupported in MySQL 5?) It is another tool that helps you design databases that only take logical and sensical data. Let’s create a table of employees and how much they get paid:

CREATE TABLE employee (
    employee_id SERIAL
        PRIMARY KEY,
    monthly_salary NUMERIC
        NOT NULL
);

This table works great, until an employee complains about the payroll system deducting money from his bank account. A quick look shows that the HR director and his fat fingers are the cause once again — he entered $-640 into the payroll application! He must have been aiming for the 9 key, you figure. Let’s put a check constraint onto that column to prevent this from ever happening again. While we’re at it, let’s limit the monthly salary to values under $15,000. You can always come back and change it later if necessary, but for now it will prevent another data entry typo.

CREATE TABLE employee (
    employee_id SERIAL
        PRIMARY KEY,
    monthly_salary NUMERIC
        NOT NULL
        CHECK (monthly_salary > 0)
        CHECK (monthly_salary < 15000)
);

Now we cannot enter salary values outside of (0 ... 15000) per month. You might need to rebuild this table and increase the salary limit, once your boss finds out how you prevented a terrible payroll mistake. ... ha ha. ha.

Vancouver Python Workshop

My friend Bradley is putting on a talk at “VanPy” entitled “Rapid Development of Enterprise-Level Web Applications”. It is going to be an interesting case study of a large web application that was re-developed in Python over a couple of years. The application went from ASP and Windows based to Python and Linux – yay! For anyone who has never see a Python talk that has to do with an Oracle database (*gasp – not MySQL? 😉 *), and terabytes of data, this is your chance.

Hopefully Jim Hugunin’s IronPython talk won’t steal too much of the potential audience away.

I’ll be the rude guy in the back of the room making silly faces.

1 2 3 4 5 15