Created
December 16, 2018 11:30
-
-
Save QuantumDamage/aae0ea557b2b8db3e44556dbddad3cb4 to your computer and use it in GitHub Desktop.
SQLite i Python – czy warto?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"cells": [ | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 1\nimport sqlite3\nimport random\nimport time\nimport pandas as pd\nimport numpy as np", | |
"execution_count": 1, | |
"outputs": [] | |
}, | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 2\nconn = sqlite3.connect('../output/example.db')\nc = conn.cursor()", | |
"execution_count": 2, | |
"outputs": [] | |
}, | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 3\nc.execute('''CREATE TABLE IF NOT EXISTS readings (date text, PM25 real, PM10 real)''')", | |
"execution_count": 3, | |
"outputs": [ | |
{ | |
"output_type": "execute_result", | |
"execution_count": 3, | |
"data": { | |
"text/plain": "<sqlite3.Cursor at 0x7f78f7ba1ab0>" | |
}, | |
"metadata": {} | |
} | |
] | |
}, | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 4\ncounter = 0\nwhile True:\n readings = {}\n readings[\"PM25\"] = round(random.uniform(0, 121),1)\n readings[\"PM10\"] = round(random.uniform(0, 201),1)\n readings[\"datetime\"] = time.strftime('%Y-%m-%d %H:%M:%S')\n \n c.execute(\"INSERT INTO readings VALUES (?,?,?)\", (readings[\"datetime\"], readings[\"PM25\"], readings[\"PM10\"]))\n \n conn.commit()\n \n counter += 1\n if counter == 10:\n break", | |
"execution_count": 4, | |
"outputs": [] | |
}, | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 5\nfor row in c.execute('SELECT * FROM readings ORDER BY PM25 DESC LIMIT 10'):\n print(row)", | |
"execution_count": 5, | |
"outputs": [ | |
{ | |
"output_type": "stream", | |
"text": "('2018-12-16 11:18:08', 120.2, 53.4)\n('2018-12-16 11:18:07', 117.2, 14.6)\n('2018-12-16 09:22:54', 116.6, 186.9)\n('2018-12-16 12:25:49', 109.8, 189.4)\n('2018-12-16 09:22:54', 108.8, 77.6)\n('2018-12-16 11:18:07', 100.5, 160.5)\n('2018-12-16 12:25:50', 99.5, 10.6)\n('2018-12-16 09:24:15', 92.9, 27.4)\n('2018-12-16 09:22:53', 87.1, 180.8)\n('2018-12-16 11:18:08', 85.6, 40.4)\n", | |
"name": "stdout" | |
} | |
] | |
}, | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 6\ndf = pd.read_sql_query(\"select * from readings\", conn)", | |
"execution_count": 6, | |
"outputs": [] | |
}, | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 7\ndf[\"additional\"] = np.where(df[\"PM10\"] > 100, \"Alert!\", None)\ndf.to_sql(\"readings_modified\", conn, if_exists=\"replace\", index=False)\nfor row in c.execute('SELECT * FROM readings_modified ORDER BY PM25 DESC LIMIT 10'):\n print(row)", | |
"execution_count": 7, | |
"outputs": [ | |
{ | |
"output_type": "stream", | |
"text": "('2018-12-16 11:18:08', 120.2, 53.4, None)\n('2018-12-16 11:18:07', 117.2, 14.6, None)\n('2018-12-16 09:22:54', 116.6, 186.9, 'Alert!')\n('2018-12-16 12:25:49', 109.8, 189.4, 'Alert!')\n('2018-12-16 09:22:54', 108.8, 77.6, None)\n('2018-12-16 11:18:07', 100.5, 160.5, 'Alert!')\n('2018-12-16 12:25:50', 99.5, 10.6, None)\n('2018-12-16 09:24:15', 92.9, 27.4, None)\n('2018-12-16 09:22:53', 87.1, 180.8, 'Alert!')\n('2018-12-16 11:18:08', 85.6, 40.4, None)\n", | |
"name": "stdout" | |
} | |
] | |
}, | |
{ | |
"metadata": { | |
"trusted": true | |
}, | |
"cell_type": "code", | |
"source": "# Kod 8\nconn.close()", | |
"execution_count": 8, | |
"outputs": [] | |
} | |
], | |
"metadata": { | |
"kernelspec": { | |
"name": "conda-env-jakbadacdane.pl-py", | |
"display_name": "Python [conda env:jakbadacdane.pl]", | |
"language": "python" | |
}, | |
"language_info": { | |
"name": "python", | |
"version": "3.6.7", | |
"mimetype": "text/x-python", | |
"codemirror_mode": { | |
"name": "ipython", | |
"version": 3 | |
}, | |
"pygments_lexer": "ipython3", | |
"nbconvert_exporter": "python", | |
"file_extension": ".py" | |
}, | |
"gist": { | |
"id": "", | |
"data": { | |
"description": " SQLite i Python – czy warto?", | |
"public": true | |
} | |
} | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 2 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment