Trading in options and futures pdf38 comments
Trading reddit rocket league xbox one cross platform
Modules are available for most of the popular relational databases and a number of non-relational databases as well.
In this presentation to OSDC I'll talk about how you can use Python to access data stored in databases and the various tools and technologies available to help you. Primarily I'll be focussing on relational databases, although you can use Python to access other data stores. I'll also, for illustration, refer to the ways that databases are accessed and used in other programming languages such as Java, Perl and PHP.
Because it is a pythonic API it fits nicely into existing Python code and allows Python programmers to easily store and retrieve data from databases. This gives the advantage that there is a standard way to write code that deals with a database using connections, cursors and transactions.
It also defines a standard exception hierarchy that modules must implement. Each of these latter features have multiple implementation options. However there are parts of the standard with multiple implementations and the presence of optional features means that writing cross database code is rather problematic.
The addition of inconsistencies in SQL support of the different databases makes this is less of a problem than it could be. There are a number of Python modules that build on the foundations of the DB-API with different levels of abstraction.
They range from simple result set wrappers to full blown object relational mappers. The simple wrappers typically wrap the results of database operations in more Pythonic data structures like dictionaries whilst the object relational mappers allow Python programmers to largely distance themselves from writing SQL statements.
This paper assumes only a passing acquaintance with either Python or relational databases and particularly SQL. A little knowledge of either subject will certainly help though.
If you wish to follow along with the examples shown here you will need some software installed on your computer. At the very minimal you will need Python, a database and the appropriate Python database module. A good place to start is the SQLite database and the accompanying pysqlite2 Python database module. These are the tools we will use in the example code in this paper. The code should work with most recent releases of Python although I suspect that you will have problems with releases before version 2.
If you've got Python 2. Best described as an Agile language, Python was first released in Python has an extensive standard library but access to databases is available mainly in third party modules. The standard library has more low level storage interfaces like dbm, pickle and shelve. The definition of a database is a persistent store for your data.
I'd extend that to only include those that conform to the ACID principle for transactions. When most people talk about database these days they are referring to relational databases also known as RDBMS which are persistent data stores that implement an interface based on the relational calculus.
In a relational database everything is a relation. Tables are relations, as are query results. The relational calculus then defines a number of operations that operate on relations, giving rise to the set based, declarative language SQL. Practically, relational databases are made up of tables. Each table has a number of columns with defined data types and precisions.
Each table will contain zero, one or more rows which are something like instances of objects. But modules complying to this standard are few and far between. The reason for this is that it was then revised and a version 2.
But unlike PHP where each database driver implements its own often slightly different commands for interacting with the database in Python there is a level of consistency between modules. If you have a database that you already use the chances are that there is a Python database module for it. It also acts as the owner of a number of standard exceptions. So, for our SQLite database we would do something like:. To see the available methods and attributes on your connection use Python's introspection features:.
Certain methods are available on the connection object returned by this constructor. They all relate to 'global' operations for transaction control such as commit or rollback and most importantly allow us to create cursor objects. Each connection can have multiple cursors.
Generally you'll create one for each series of transactions, although it is perfectly common just to create one per connection. As a rule you should create one cursor for each concurrent transaction or group of transactions. We create cursors with a call to the constructor method on the connection:.
A cursor object is the means by which we issue SQL statements to our database and then get the results. To run a specific SQL statement use the execute method:. Transaction control is effected through our connection object, so to commit this change we use the commit method:.
Then we need to be able to get our data back again. We can continue to use our original cursor as we don't need to keep the results of any prior operations around. Getting our data is a two step process:. They are fetchone , fetchmany and fetchall. They pretty much do what they say fetching one row from the result set, a group of rows or every row that your query will return in one step.
Obviously the fetchall method should be avoided when you are likely to have very big result sets as it may take a long time to return any data. Note that the 'fetch' methods only have to return a sequence. In Python any number of data types are classified as sequences so don't assume that you will always get a tuple or a list. The DB-API provides a basic standard level of functionality enabling Python programs that deal with databases to be quite similar in structure and content.
This isn't a as big a problem as it first seems because rarely do two different databases implement the same functionality and when they do it is rarely through exactly the same interface.
The specification authors took the view that the DB-API would be like the SQL standard, specifying a core of standard functionality and recognising that different databases would need different code to support their different extensions.
It was better to provide some flexibility in implementation because this reflects the reality that is modern databases. One of the trickiest things people new to databases and Python get into trouble with is bind parameters. The typical first use scenario of parameters is something like:.
Which has two main problems. Proper use of bind variables and parameters addresses both of these problems. Lets try another insert into our table:. In this case the database is more likely to keep the parsed version of stmt around and save a few machine cycles on the second insert. Because we are passing the values as explicit parameters the DB-API module can properly escape the contents and reduce the likelihood of malicious or accidental damage to our database.
The module author is free to support one or more of the five available styles:. The format option provides all kinds of opportunities for trouble. Consider these two examples:.
One is good practice, the other bad but the visible difference is very subtle. To execute these statements you would do something like:. Again, semantically worlds apart but syntactically quite similar. The bad news is that there isn't a target date for its release. From simple helpers like dtuple.
There is a tool for every need. For a list of these modules your best bet is the higher level database programming page on the Python Wiki. Of most benefit to the new or casual user are helpers like dtuple. This module by Greg Stein allows you to deal with the result sets that are returned from cursors as a dictionary or an object rather than a sequence.
Programmers coming from an object oriented background often don't want to write SQL. These ORMs enable a table centric view of your database allowing you to describe your tables in code or to read them from the database data dictionary.
SQLAlchemy then adds a number of different ways of mapping these objects to your application whereas SQLObject leaves you to define them yourself. Operations on these DTOs are transparently echoed into your database by the services they provide.
And the equivalent operations with SQLObject:. The advantage of these tools is that they can initially make your application code simpler. By letting the application code interact only with Python objects you can worry about solving the problems your application is aimed at and don't have to deal with the object relational impedance mismatch. The drawback is that the compromises they make in transaction management and generalising between different databases may mean that they actually end up making your application code more complex than it needs to be.
In cases where you just need the ability to persist objects instances as rows in a table ORMs can provide incredible boosts to productivity.
An example of this can be seen in the applications produced by Django and Ruby on Rails. To avoid situations where the requirements for your application aren't met by standard functionality both of the ORMs mentioned here allow you to drop down to raw SQL when and where you need to. This provides a great compromise between the object view that will be prevalent in your application and the set based approach that is the strength of the relational database.
Of course, you don't need to be a disciple of Ted Codd to want to persistently store data from your Python programs. The standard library provides the pickle and shelve modules which are perfectly suited to saving Python objects to the file system. They are quite low level and don't provide much support in the way of complex transactions, multi user access and network access. To this end there are a number of object oriented database modules available on the Python platform.
I'm not going to cover them in this paper but do check out:. Hopefully this paper will have whetted your appetite for all things Python and database. If you want to find out more here are some suggestions to start with:.