<jupyter_start><jupyter_text>Среда выполнения - Перезапустить среду выполнения<jupyter_code>import sqlite3
sqlite3.sqlite_version
import pandas as pd
import numpy as np
con = sqlite3.connect('db')
cur = con.cursor()
def select(sql):
  return pd.read_sql(sql,con)<jupyter_output><empty_output><jupyter_text>Increment<jupyter_code>sql = '''with rec(x) as (
select 1
union all
select t.x + 1 from rec t
where t.x < 5
)
select * from rec t'''
select(sql)<jupyter_output><empty_output><jupyter_text>Dates<jupyter_code>sql = '''with dates(dt) as (
select date('2021-03-01')
union all
select date(t.dt,'+1 month') as dt from dates t
limit 5
)
select * from dates t'''
select(sql)<jupyter_output><empty_output><jupyter_text>Fibonacci number<jupyter_code>sql = '''with fib(n1,n2,n3) as (
select null,1,1
union all
select 
t.n2 as n1, 
t.n3 as n2,
t.n2 + t.n3 as n3
from fib t
limit 5
)
select * from fib t'''
select(sql)<jupyter_output><empty_output><jupyter_text>Running total<jupyter_code>df = pd.DataFrame({'id':[1,2,3,4,5,6],'num':[1,5,4,9,14,27]})
df.to_sql('seq',con,index=False,if_exists='replace')
sql = '''with running(id,num,rt) as (
select t.id,t.num, t.num  from seq t
where t.id = 1
union all
select s.id,s.num, t.rt + s.num as rt from running t
join seq s on t.id = s.id - 1
limit 10
)
select * from running t'''
select(sql)<jupyter_output><empty_output><jupyter_text>Report to manager<jupyter_code>sql = '''
drop table if exists employees;
Create table If Not Exists employees (employee_id int, employee_name varchar(30), manager_id int)
insert into Employees (employee_id, employee_name, manager_id) values ('1', 'Boss', '1')
insert into Employees (employee_id, employee_name, manager_id) values ('3', 'Alice', '3')
insert into Employees (employee_id, employee_name, manager_id) values ('2', 'Bob', '1')
insert into Employees (employee_id, employee_name, manager_id) values ('4', 'Daniel', '2')
insert into Employees (employee_id, employee_name, manager_id) values ('7', 'Luis', '4')
insert into Employees (employee_id, employee_name, manager_id) values ('8', 'John', '3')
insert into Employees (employee_id, employee_name, manager_id) values ('9', 'Angela', '8')
insert into Employees (employee_id, employee_name, manager_id) values ('77', 'Robert', '1')'''
sql = ';\n'.join(sql.split('\n'))
cur.executescript(sql)
sql = '''select * from employees t'''
select(sql)
sql = '''with rec as (
select t.*, 0 as depth from employees t
where t.employee_id = 1
union all
select e.employee_id, e.employee_name, e.manager_id, t.depth + 1 from rec t
join employees e on t.employee_id = e.manager_id and e.employee_id != 1
limit 100
)
select * from rec t
where t.depth <= 3'''
select(sql)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14706966457795798, 'fraction_numerical': 0.02727607814227792, 'mean_word_length': 6.19893899204244, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '160810', 'license': 'MIT', 'n_tokens_mistral': 1072, 'n_tokens_neox': 1007, 'n_words': 289, 'path': 'recursive/google_colab_sqlite_recursive.ipynb', 'repo_name': 'grishenkovp/sqlite', 'size': '2713'}