Using sqllite3 to store static and time series data
I've had a request to help out with the usage of sqlite in python to save information for systematic trading. There are three varieties of records I normally preserve; static data, country information and timeseries records.
Static facts include:
- Futures, and specific contract details
- System parameters
- Price data
- Volume data
- Fundamental data if used, eg PE ratios for equities
- Accounting data
- Diagnostic data, storing what the system did at various points in the past
State information relates to the manage of the trading device and I may not be going into information right here - its just static facts that's regularly changed.
Code is within the ordinary git repo. You will needpandas and sqllite3 (which got here with my python distro mechanically so test your very own).
Creating the database
dbname="mydb"dbfilename=get_db_filename(dbname)
setup_blank_tables(dbfilename, ["CREATE TABLE timeseries (datetime text, code text, price float)",
"CREATE TABLE static (code text, fullname text)"])
Here we are developing a database document with tables in it. Notice using database names to abstract away from wherein they are stored. I locate the performance of sqllite3 with big files isn't superb so I have a tendency to paste to one table consistent with report in practice, but for this easy instance we don't want to.
If you are a SQL whizz you will see that I am no longer doing any relational stuff here.
Static facts
st_table=staticdata(dbname)st_table.Add("FTSE", "FTSE a hundred index")
st_table.Alter("FTSE", "FTSE all percentage")
print st_table.Study("FTSE")
st_table.Delete("FTSE")
Notice that we use staticdata so we don't need to use any SQL in these commands (in case the underlying table structure changes and to avoid having reams of repetitive nonsense), and within that the connection object ensures that the staticdata code isn't specific to sqlite3.
The sqlite3 read returns lists of tuples, which staticdata.read() resolves to a single string.
Timeseries statistics
dt_table=tsdata(dbname)someprices=pd.TimeSeries(variety(a hundred), pd.Date_range('1/1/2014', intervals=one hundred))
dt_table.Add("FTSE", someprices)
dt_table.Examine("FTSE")
We use pandas TimeSeries elegance as the enter and output, that's then translated into database terms. Sqlite has no local datetime layout, simplest textual content or glide, so we need to translate among pandas/datetime and text. I outline a particular format for the text representation to be precise and ensure the database is ahead compatible to any adjustments in pandas.
