Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLite i Python – czy warto?
{
"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
You can’t perform that action at this time.