{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"import sys\n",
"import datetime"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"trainfile = \"../data/ozone-la-exogrenous.csv\"\n",
"\n",
"cols = [\"Date\", \"Month\", \"Exog2\", \"Exog3\", \"Exog4\", \"Ozone\"];\n",
" \n",
"df_train = pd.read_csv(trainfile, names = cols, sep=r',', engine='python', skiprows=1);\n",
"df_train['Time'] = df_train['Date'].apply(lambda x : datetime.datetime.strptime(x, \"%Y-%m\"))"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"df_train.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"df_train_dummies = pd.get_dummies(df_train)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"df_train_dummies.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"lVC = df_train['Exog4'].value_counts()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"lVC.head(100)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"lVC.index[0:5].tolist()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from sqlalchemy import *\n",
"#from sqlalchemy import desc, nullsfirst\n",
"import sqlalchemy\n",
"from sqlalchemy.sql import table, column, select"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"# in-memory database\n",
"#lDSN = 'sqlite://'\n",
"#lDSN = 'mysql://user:pass@localhost/GitHubtest'\n",
"lDSN = 'postgresql:///GitHubtest'\n",
"engine = create_engine(lDSN , echo=True)\n",
"#create_engine( , echo=True)\n",
"conn = engine.connect()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"df_train.to_sql(\"ds1\" , conn, if_exists='replace')"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"meta = MetaData()\n",
"table2 = Table('ds1', meta, autoload=True, autoload_with=engine)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"table2.c['Month'].type"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"table2.c['Exog2'].type, table2.c['Exog3'].type, table2.c['Exog4'].type, table2.c['Time'].type "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"dt1 = datetime.datetime(1955, 7, 1, 0, 0); dt2 = datetime.datetime(1965, 7, 1, 0, 0)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"dt1 , dt2, str(dt1), str(dt2)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"def getDateTimeLiteral(iValue):\n",
" #return sqlalchemy.sql.expression.literal(iValue, sqlalchemy.types.TIMESTAMP);\n",
" return sqlalchemy.sql.expression.literal(str(iValue), sqlalchemy.types.String);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"expr = getDateTimeLiteral(dt2) - getDateTimeLiteral(dt2)\n",
"expr2 = getDateTimeLiteral(dt2)\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"expr.type, expr2.type"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"stmt = select([expr, expr2])"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"def generate_Sql(statement):\n",
" return statement.compile(bind=engine, compile_kwargs={'literal_binds': True}).string;\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"generate_Sql(stmt)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"print(str(stmt))"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"print(str(stmt.compile(engine)))"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"from sqlalchemy.dialects import postgresql\n",
"print(stmt.compile(dialect=postgresql.dialect(), compile_kwargs={\"literal_binds\": True}))"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.5.2+"
}
},
"nbformat": 4,
"nbformat_minor": 0
}