Skip to content

Commit abc4e03

Browse files
stonebigstonebig
authored
Merge pull request #90 from stonebig/master
doc update
2 parents e3ffef2 + 87b4893 commit abc4e03

File tree

2 files changed

+620
-0
lines changed

2 files changed

+620
-0
lines changed

‎docs/baresql_experiments.ipynb‎

Lines changed: 376 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,376 @@
1+
{
2+
"cells": [
3+
{
4+
"cell_type": "markdown",
5+
"metadata": {},
6+
"source": [
7+
"## Baresql : an attempt to make a better sql handling of Python datas\n",
8+
"\n",
9+
"Tips:\n",
10+
" - zz$$ is to create temporarly a table zz in SQLite from Python list or Dataframe zz\n",
11+
" - :z (otherwise $z legacy) is a variable to use in Sqlite query from Python Variable z\n",
12+
" - use \"PERSIST zz\" to keep a Python table in the Database between 2 calls\n",
13+
" - use pydef to create python function in SQLite:\n",
14+
" . \"pydef\" is the beginning of the function, first semicolumn \";\" the end of the function\n",
15+
" . every in & out paramater of a Python function must be a string\n",
16+
" \n",
17+
"Other packages in same domain: ipython-sql, pandasql, sqlite-bro, db.py, jupyterlab-sql\n",
18+
" "
19+
]
20+
},
21+
{
22+
"cell_type": "code",
23+
"execution_count": 1,
24+
"metadata": {},
25+
"outputs": [
26+
{
27+
"name": "stdout",
28+
"output_type": "stream",
29+
"text": [
30+
"*** def py_sqrt(s):\n",
31+
" \"sqrt function from Python Math standard library\"\n",
32+
" import math as py_math\n",
33+
" return (\"%s\" % py_math.sqrt(float(s))) ***\n"
34+
]
35+
},
36+
{
37+
"data": {
38+
"text/html": [
39+
"<div>\n",
40+
"<style scoped>\n",
41+
" .dataframe tbody tr th:only-of-type {\n",
42+
" vertical-align: middle;\n",
43+
" }\n",
44+
"\n",
45+
" .dataframe tbody tr th {\n",
46+
" vertical-align: top;\n",
47+
" }\n",
48+
"\n",
49+
" .dataframe thead th {\n",
50+
" text-align: right;\n",
51+
" }\n",
52+
"</style>\n",
53+
"<table border=\"1\" class=\"dataframe\">\n",
54+
" <thead>\n",
55+
" <tr style=\"text-align: right;\">\n",
56+
" <th></th>\n",
57+
" <th>py_sqrt(sum(x*x) / (count(*) -1))</th>\n",
58+
" <th>just_to_check</th>\n",
59+
" <th>:titi</th>\n",
60+
" </tr>\n",
61+
" </thead>\n",
62+
" <tbody>\n",
63+
" <tr>\n",
64+
" <td>0</td>\n",
65+
" <td>1.5811388300841898</td>\n",
66+
" <td>7</td>\n",
67+
" <td>9</td>\n",
68+
" </tr>\n",
69+
" </tbody>\n",
70+
"</table>\n",
71+
"</div>"
72+
],
73+
"text/plain": [
74+
" py_sqrt(sum(x*x) / (count(*) -1)) just_to_check :titi\n",
75+
"0 1.5811388300841898 7 9"
76+
]
77+
},
78+
"execution_count": 1,
79+
"metadata": {},
80+
"output_type": "execute_result"
81+
}
82+
],
83+
"source": [
84+
"from baresql import baresql\n",
85+
"bsql = baresql.baresql()\n",
86+
"bsqldf = lambda q: bsql.df(q, dict(globals(),**locals()))\n",
87+
"\n",
88+
"datas = (1 , 2 , 3 , 4, 5 )\n",
89+
"toto = 7\n",
90+
"titi = 9\n",
91+
"sql=\"\"\"\n",
92+
"pydef py_sqrt(s):\n",
93+
" \"sqrt function from Python Math standard library\"\n",
94+
" import math as py_math\n",
95+
" return (\"%s\" % py_math.sqrt(float(s)));\n",
96+
"with delta(x) as (select c0 - (avg(c0) over() ) from datas$$)\n",
97+
" select py_sqrt(sum(x*x) / (count(*) -1)) , $toto as just_to_check , :titi from delta\"\"\"\n",
98+
"bsqldf(sql)"
99+
]
100+
},
101+
{
102+
"cell_type": "code",
103+
"execution_count": 2,
104+
"metadata": {},
105+
"outputs": [
106+
{
107+
"data": {
108+
"text/plain": [
109+
"1.5811388300841898"
110+
]
111+
},
112+
"execution_count": 2,
113+
"metadata": {},
114+
"output_type": "execute_result"
115+
}
116+
],
117+
"source": [
118+
"import statistics\n",
119+
"statistics.stdev(datas)"
120+
]
121+
},
122+
{
123+
"cell_type": "code",
124+
"execution_count": 3,
125+
"metadata": {},
126+
"outputs": [
127+
{
128+
"name": "stdout",
129+
"output_type": "stream",
130+
"text": [
131+
"*** def py_quad(s): return ('%s' % float(s)**4) ***\n"
132+
]
133+
},
134+
{
135+
"data": {
136+
"text/html": [
137+
"<div>\n",
138+
"<style scoped>\n",
139+
" .dataframe tbody tr th:only-of-type {\n",
140+
" vertical-align: middle;\n",
141+
" }\n",
142+
"\n",
143+
" .dataframe tbody tr th {\n",
144+
" vertical-align: top;\n",
145+
" }\n",
146+
"\n",
147+
" .dataframe thead th {\n",
148+
" text-align: right;\n",
149+
" }\n",
150+
"</style>\n",
151+
"<table border=\"1\" class=\"dataframe\">\n",
152+
" <thead>\n",
153+
" <tr style=\"text-align: right;\">\n",
154+
" <th></th>\n",
155+
" <th>py_quad(2)</th>\n",
156+
" </tr>\n",
157+
" </thead>\n",
158+
" <tbody>\n",
159+
" <tr>\n",
160+
" <td>0</td>\n",
161+
" <td>16.0</td>\n",
162+
" </tr>\n",
163+
" </tbody>\n",
164+
"</table>\n",
165+
"</div>"
166+
],
167+
"text/plain": [
168+
" py_quad(2)\n",
169+
"0 16.0"
170+
]
171+
},
172+
"execution_count": 3,
173+
"metadata": {},
174+
"output_type": "execute_result"
175+
}
176+
],
177+
"source": [
178+
"# defining a pydef function\n",
179+
"bsqldf(\"pydef py_quad(s): return ('%s' % float(s)**4)\")\n",
180+
"bsqldf(\"select py_quad(2)\")"
181+
]
182+
},
183+
{
184+
"cell_type": "code",
185+
"execution_count": 14,
186+
"metadata": {},
187+
"outputs": [
188+
{
189+
"data": {
190+
"text/html": [
191+
"<div>\n",
192+
"<style scoped>\n",
193+
" .dataframe tbody tr th:only-of-type {\n",
194+
" vertical-align: middle;\n",
195+
" }\n",
196+
"\n",
197+
" .dataframe tbody tr th {\n",
198+
" vertical-align: top;\n",
199+
" }\n",
200+
"\n",
201+
" .dataframe thead th {\n",
202+
" text-align: right;\n",
203+
" }\n",
204+
"</style>\n",
205+
"<table border=\"1\" class=\"dataframe\">\n",
206+
" <thead>\n",
207+
" <tr style=\"text-align: right;\">\n",
208+
" <th></th>\n",
209+
" <th>index</th>\n",
210+
" <th>c0</th>\n",
211+
" </tr>\n",
212+
" </thead>\n",
213+
" <tbody>\n",
214+
" <tr>\n",
215+
" <td>0</td>\n",
216+
" <td>0</td>\n",
217+
" <td>1.0</td>\n",
218+
" </tr>\n",
219+
" <tr>\n",
220+
" <td>1</td>\n",
221+
" <td>1</td>\n",
222+
" <td>2.0</td>\n",
223+
" </tr>\n",
224+
" <tr>\n",
225+
" <td>2</td>\n",
226+
" <td>2</td>\n",
227+
" <td>3.0</td>\n",
228+
" </tr>\n",
229+
" </tbody>\n",
230+
"</table>\n",
231+
"</div>"
232+
],
233+
"text/plain": [
234+
" index c0\n",
235+
"0 0 1.0\n",
236+
"1 1 2.0\n",
237+
"2 2 3.0"
238+
]
239+
},
240+
"execution_count": 14,
241+
"metadata": {},
242+
"output_type": "execute_result"
243+
}
244+
],
245+
"source": [
246+
"# use persist to keep tables between 2 request\n",
247+
"datas = (1 , 2 , 3 , 4, 5 )\n",
248+
"datas2 =(1, 2, 3, 4, 5, 6, 7)\n",
249+
"datas3 =(1, 2, 3,)\n",
250+
"\n",
251+
"bsqldf(\"PERSIST datas, datas2; select * from datas3$$\")"
252+
]
253+
},
254+
{
255+
"cell_type": "code",
256+
"execution_count": 15,
257+
"metadata": {},
258+
"outputs": [
259+
{
260+
"data": {
261+
"text/html": [
262+
"<div>\n",
263+
"<style scoped>\n",
264+
" .dataframe tbody tr th:only-of-type {\n",
265+
" vertical-align: middle;\n",
266+
" }\n",
267+
"\n",
268+
" .dataframe tbody tr th {\n",
269+
" vertical-align: top;\n",
270+
" }\n",
271+
"\n",
272+
" .dataframe thead th {\n",
273+
" text-align: right;\n",
274+
" }\n",
275+
"</style>\n",
276+
"<table border=\"1\" class=\"dataframe\">\n",
277+
" <thead>\n",
278+
" <tr style=\"text-align: right;\">\n",
279+
" <th></th>\n",
280+
" <th>type</th>\n",
281+
" <th>name</th>\n",
282+
" <th>tbl_name</th>\n",
283+
" <th>rootpage</th>\n",
284+
" <th>sql</th>\n",
285+
" </tr>\n",
286+
" </thead>\n",
287+
" <tbody>\n",
288+
" <tr>\n",
289+
" <td>0</td>\n",
290+
" <td>table</td>\n",
291+
" <td>datas</td>\n",
292+
" <td>datas</td>\n",
293+
" <td>4</td>\n",
294+
" <td>CREATE TABLE \"datas\" (\\n\"index\" INTEGER,\\n \"c...</td>\n",
295+
" </tr>\n",
296+
" <tr>\n",
297+
" <td>1</td>\n",
298+
" <td>index</td>\n",
299+
" <td>ix_datas_index</td>\n",
300+
" <td>datas</td>\n",
301+
" <td>5</td>\n",
302+
" <td>CREATE INDEX \"ix_datas_index\"ON \"datas\" (\"index\")</td>\n",
303+
" </tr>\n",
304+
" <tr>\n",
305+
" <td>2</td>\n",
306+
" <td>table</td>\n",
307+
" <td>datas2</td>\n",
308+
" <td>datas2</td>\n",
309+
" <td>7</td>\n",
310+
" <td>CREATE TABLE \"datas2\" (\\n\"index\" INTEGER,\\n \"...</td>\n",
311+
" </tr>\n",
312+
" <tr>\n",
313+
" <td>3</td>\n",
314+
" <td>index</td>\n",
315+
" <td>ix_datas2_index</td>\n",
316+
" <td>datas2</td>\n",
317+
" <td>3</td>\n",
318+
" <td>CREATE INDEX \"ix_datas2_index\"ON \"datas2\" (\"in...</td>\n",
319+
" </tr>\n",
320+
" </tbody>\n",
321+
"</table>\n",
322+
"</div>"
323+
],
324+
"text/plain": [
325+
" type name tbl_name rootpage \\\n",
326+
"0 table datas datas 4 \n",
327+
"1 index ix_datas_index datas 5 \n",
328+
"2 table datas2 datas2 7 \n",
329+
"3 index ix_datas2_index datas2 3 \n",
330+
"\n",
331+
" sql \n",
332+
"0 CREATE TABLE \"datas\" (\\n\"index\" INTEGER,\\n \"c... \n",
333+
"1 CREATE INDEX \"ix_datas_index\"ON \"datas\" (\"index\") \n",
334+
"2 CREATE TABLE \"datas2\" (\\n\"index\" INTEGER,\\n \"... \n",
335+
"3 CREATE INDEX \"ix_datas2_index\"ON \"datas2\" (\"in... "
336+
]
337+
},
338+
"execution_count": 15,
339+
"metadata": {},
340+
"output_type": "execute_result"
341+
}
342+
],
343+
"source": [
344+
"bsqldf(\"select * from sqlite_master\")"
345+
]
346+
},
347+
{
348+
"cell_type": "code",
349+
"execution_count": null,
350+
"metadata": {},
351+
"outputs": [],
352+
"source": []
353+
}
354+
],
355+
"metadata": {
356+
"kernelspec": {
357+
"display_name": "Python 3",
358+
"language": "python",
359+
"name": "python3"
360+
},
361+
"language_info": {
362+
"codemirror_mode": {
363+
"name": "ipython",
364+
"version": 3
365+
},
366+
"file_extension": ".py",
367+
"mimetype": "text/x-python",
368+
"name": "python",
369+
"nbconvert_exporter": "python",
370+
"pygments_lexer": "ipython3",
371+
"version": "3.7.4"
372+
}
373+
},
374+
"nbformat": 4,
375+
"nbformat_minor": 4
376+
}

0 commit comments

Comments
 (0)