325 lines
8.5 KiB
Plaintext
325 lines
8.5 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 10,
|
|
"id": "7f0fc0f0",
|
|
"metadata": {
|
|
"scrolled": true
|
|
},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"The sql extension is already loaded. To reload it, use:\n",
|
|
" %reload_ext sql\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"# enable sql\n",
|
|
"!pip install ipython-sql >> /dev/null\n",
|
|
"%load_ext sql"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 8,
|
|
"id": "412a7b61",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# make a database, let's call it mollie\n",
|
|
"%sql sqlite:///mollie.sqlite3"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 9,
|
|
"id": "dc56dd7c",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# import our mollie CSVs\n",
|
|
"!sqlite3 mollie.sqlite3 -separator \",\" '.import ./customers.csv mollie_customers'\n",
|
|
"!sqlite3 mollie.sqlite3 -separator \",\" '.import ./payments.csv mollie_payments'\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 36,
|
|
"id": "f59b6a11",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"CREATE TABLE IF NOT EXISTS \"mollie_customers\"(\n",
|
|
" \"resource\" TEXT,\n",
|
|
" \"id\" TEXT,\n",
|
|
" \"mode\" TEXT,\n",
|
|
" \"name\" TEXT,\n",
|
|
" \"email\" TEXT,\n",
|
|
" \"locale\" TEXT,\n",
|
|
" \"metadata\" TEXT,\n",
|
|
" \"createdAt\" TEXT,\n",
|
|
" \"_links\" TEXT\n",
|
|
");\n",
|
|
"CREATE TABLE IF NOT EXISTS \"mollie_payments\"(\n",
|
|
" \"resource\" TEXT,\n",
|
|
" \"id\" TEXT,\n",
|
|
" \"createdAt\" TEXT,\n",
|
|
" \"paidAt\" TEXT,\n",
|
|
" \"description\" TEXT,\n",
|
|
" \"method\" TEXT,\n",
|
|
" \"status\" TEXT,\n",
|
|
" \"isCancelable\" TEXT,\n",
|
|
" \"sequenceType\" TEXT,\n",
|
|
" \"profileId\" TEXT,\n",
|
|
" \"customerId\" TEXT,\n",
|
|
" \"mandateId\" TEXT,\n",
|
|
" \"details.consumerName\" TEXT,\n",
|
|
" \"details.consumerAccount\" TEXT,\n",
|
|
" \"details.consumerBic\" TEXT,\n",
|
|
" \"amount.value\" TEXT,\n",
|
|
" \"amount.currency\" TEXT,\n",
|
|
" \"settlementAmount.value\" TEXT,\n",
|
|
" \"settlementAmount.currency\" TEXT,\n",
|
|
" \"amountRefunded.value\" TEXT,\n",
|
|
" \"amountRefunded.currency\" TEXT,\n",
|
|
" \"amountRemaining.value\" TEXT,\n",
|
|
" \"amountRemaining.currency\" TEXT,\n",
|
|
" \"customer.resource\" TEXT,\n",
|
|
" \"customer.mode\" TEXT,\n",
|
|
" \"customer.name\" TEXT,\n",
|
|
" \"customer.email\" TEXT,\n",
|
|
" \"customer.locale\" TEXT,\n",
|
|
" \"customer.metadata\" TEXT,\n",
|
|
" \"customer.createdAt\" TEXT,\n",
|
|
" \"customer._links\" TEXT\n",
|
|
");\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"# let's check out their data structures\n",
|
|
"!sqlite3 mollie.sqlite3 '.schema mollie_customers'\n",
|
|
"!sqlite3 mollie.sqlite3 '.schema mollie_payments'\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 34,
|
|
"id": "5c11902f",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
" sqlite://\n",
|
|
" * sqlite:///mollie.sqlite3\n",
|
|
"Done.\n"
|
|
]
|
|
},
|
|
{
|
|
"data": {
|
|
"text/html": [
|
|
"<table>\n",
|
|
" <tr>\n",
|
|
" <th>resource</th>\n",
|
|
" <th>id</th>\n",
|
|
" <th>mode</th>\n",
|
|
" <th>name</th>\n",
|
|
" <th>email</th>\n",
|
|
" <th>locale</th>\n",
|
|
" <th>metadata</th>\n",
|
|
" <th>createdAt</th>\n",
|
|
" <th>_links</th>\n",
|
|
" </tr>\n",
|
|
"</table>"
|
|
],
|
|
"text/plain": [
|
|
"[]"
|
|
]
|
|
},
|
|
"execution_count": 34,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"# preview the customers (sensitive data, so results censored from version control)\n",
|
|
"%sql select * from mollie_customers limit 10;"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 35,
|
|
"id": "627724bf",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
" sqlite://\n",
|
|
" * sqlite:///mollie.sqlite3\n",
|
|
"Done.\n"
|
|
]
|
|
},
|
|
{
|
|
"data": {
|
|
"text/html": [
|
|
"<table>\n",
|
|
" <tr>\n",
|
|
" <th>resource</th>\n",
|
|
" <th>id</th>\n",
|
|
" <th>createdAt</th>\n",
|
|
" <th>paidAt</th>\n",
|
|
" <th>description</th>\n",
|
|
" <th>method</th>\n",
|
|
" <th>status</th>\n",
|
|
" <th>isCancelable</th>\n",
|
|
" <th>sequenceType</th>\n",
|
|
" <th>profileId</th>\n",
|
|
" <th>customerId</th>\n",
|
|
" <th>mandateId</th>\n",
|
|
" <th>details.consumerName</th>\n",
|
|
" <th>details.consumerAccount</th>\n",
|
|
" <th>details.consumerBic</th>\n",
|
|
" <th>amount.value</th>\n",
|
|
" <th>amount.currency</th>\n",
|
|
" <th>settlementAmount.value</th>\n",
|
|
" <th>settlementAmount.currency</th>\n",
|
|
" <th>amountRefunded.value</th>\n",
|
|
" <th>amountRefunded.currency</th>\n",
|
|
" <th>amountRemaining.value</th>\n",
|
|
" <th>amountRemaining.currency</th>\n",
|
|
" <th>customer.resource</th>\n",
|
|
" <th>customer.mode</th>\n",
|
|
" <th>customer.name</th>\n",
|
|
" <th>customer.email</th>\n",
|
|
" <th>customer.locale</th>\n",
|
|
" <th>customer.metadata</th>\n",
|
|
" <th>customer.createdAt</th>\n",
|
|
" <th>customer._links</th>\n",
|
|
" </tr>\n",
|
|
"</table>"
|
|
],
|
|
"text/plain": [
|
|
"[]"
|
|
]
|
|
},
|
|
"execution_count": 35,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"# preview the payments (same)\n",
|
|
"%sql select * from mollie_payments limit 10;"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "de2099ba",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"%%sql\n",
|
|
"-- zoek naar mensen die het laatst hun maandelijkse contributie betaald hebben in september\n",
|
|
"select *\n",
|
|
"from mollie_customers join mollie_payments\n",
|
|
"on mollie_customers.id = mollie_payments.customerId\n",
|
|
"where mollie_payments.status = 'paid'\n",
|
|
"and mollie_payments.paidAt > '2021-09-01'\n",
|
|
"and mollie_payments.paidAt < '2021-10-01'\n",
|
|
"and mollie_payments.description like 'Maandelijkse%'\n",
|
|
"limit 50\n",
|
|
";\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 40,
|
|
"id": "7d0f0070",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
" sqlite://\n",
|
|
" * sqlite:///mollie.sqlite3\n",
|
|
"Done.\n",
|
|
"Returning data to local variable leden_maandelijkse_contributie_laatst_betaald_in_september\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"%%sql\n",
|
|
"leden_maandelijkse_contributie_laatst_betaald_in_september <<\n",
|
|
"-- zelfde query maar nou opslaan ipv preview\n",
|
|
"select *\n",
|
|
"from mollie_customers join mollie_payments\n",
|
|
"on mollie_customers.id = mollie_payments.customerId\n",
|
|
"where mollie_payments.status = 'paid'\n",
|
|
"and mollie_payments.paidAt > '2021-09-01'\n",
|
|
"and mollie_payments.paidAt < '2021-10-01'\n",
|
|
"and mollie_payments.description like 'Maandelijkse%'\n",
|
|
"-- limit 50\n",
|
|
";\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 33,
|
|
"id": "61245263",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/html": [
|
|
"<a href=\"./files/./laatst_september.csv\">CSV results</a>"
|
|
],
|
|
"text/plain": [
|
|
"CSV results at /home/jovyan/work/Downloads/./laatst_september.csv"
|
|
]
|
|
},
|
|
"execution_count": 33,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"leden_maandelijkse_contributie_laatst_betaald_in_september.csv('./laatst_september.csv')\n"
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3 (ipykernel)",
|
|
"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.9.7"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|