Sqlite Sql¶

Começando por carregar o sql no notebook

In [1]:

!pip install ipython-sql
%load_ext sql

!pip install ipython-sql %load_ext sql

Requirement already satisfied: ipython-sql in c:\python310\lib\site-packages (0.5.0)
Requirement already satisfied: prettytable in c:\python310\lib\site-packages (from ipython-sql) (3.16.0)
Requirement already satisfied: ipython in c:\python310\lib\site-packages (from ipython-sql) (8.24.0)
Requirement already satisfied: sqlalchemy>=2.0 in c:\python310\lib\site-packages (from ipython-sql) (2.0.40)
Requirement already satisfied: sqlparse in c:\python310\lib\site-packages (from ipython-sql) (0.5.1)
Requirement already satisfied: six in c:\python310\lib\site-packages (from ipython-sql) (1.16.0)
Requirement already satisfied: ipython-genutils in c:\python310\lib\site-packages (from ipython-sql) (0.2.0)
Requirement already satisfied: greenlet>=1 in c:\python310\lib\site-packages (from sqlalchemy>=2.0->ipython-sql) (3.2.1)
Requirement already satisfied: typing-extensions>=4.6.0 in c:\python310\lib\site-packages (from sqlalchemy>=2.0->ipython-sql) (4.12.2)
Requirement already satisfied: decorator in c:\python310\lib\site-packages (from ipython->ipython-sql) (5.1.1)
Requirement already satisfied: jedi>=0.16 in c:\python310\lib\site-packages (from ipython->ipython-sql) (0.19.1)
Requirement already satisfied: matplotlib-inline in c:\python310\lib\site-packages (from ipython->ipython-sql) (0.1.7)
Requirement already satisfied: prompt-toolkit<3.1.0,>=3.0.41 in c:\python310\lib\site-packages (from ipython->ipython-sql) (3.0.43)
Requirement already satisfied: pygments>=2.4.0 in c:\python310\lib\site-packages (from ipython->ipython-sql) (2.18.0)
Requirement already satisfied: stack-data in c:\python310\lib\site-packages (from ipython->ipython-sql) (0.6.3)
Requirement already satisfied: traitlets>=5.13.0 in c:\python310\lib\site-packages (from ipython->ipython-sql) (5.14.3)
Requirement already satisfied: exceptiongroup in c:\python310\lib\site-packages (from ipython->ipython-sql) (1.2.1)
Requirement already satisfied: colorama in c:\python310\lib\site-packages (from ipython->ipython-sql) (0.4.6)
Requirement already satisfied: wcwidth in c:\python310\lib\site-packages (from prettytable->ipython-sql) (0.2.13)
Requirement already satisfied: parso<0.9.0,>=0.8.3 in c:\python310\lib\site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.4)
Requirement already satisfied: executing>=1.2.0 in c:\python310\lib\site-packages (from stack-data->ipython->ipython-sql) (2.0.1)
Requirement already satisfied: asttokens>=2.1.0 in c:\python310\lib\site-packages (from stack-data->ipython->ipython-sql) (2.4.1)
Requirement already satisfied: pure-eval in c:\python310\lib\site-packages (from stack-data->ipython->ipython-sql) (0.2.2)

In [2]:

%sql sqlite:///contatos.db

%sql sqlite:///contatos.db

In [3]:

%%sql

CREATE TABLE CLIENTES (
    CLIENTE_ID INTEGER PRIMARY KEY,
    NOME TEXT NOT NULL,
    EMAIL TEXT NOT NULL UNIQUE,
    TELEFONE TEXT NOT NULL UNIQUE
);

%%sql CREATE TABLE CLIENTES ( CLIENTE_ID INTEGER PRIMARY KEY, NOME TEXT NOT NULL, EMAIL TEXT NOT NULL UNIQUE, TELEFONE TEXT NOT NULL UNIQUE );

 * sqlite:///contatos.db
Done.

Out[3]:

[]

In [4]:

%%sql

INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) 
VALUES
    ('Leonardo' , 'leonardo@gmail.com', '9999-9999' ),
    ('Rafael' , 'rafael@gmail.com', '9889-8998' ),
    ('Michelangelo' , 'michelangelo@gmail.com', '9997-7999' ),
    ('Donatello' , 'donatello@gmail.com', '9777-7779' );

%%sql INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) VALUES ('Leonardo' , 'leonardo@gmail.com', '9999-9999' ), ('Rafael' , 'rafael@gmail.com', '9889-8998' ), ('Michelangelo' , 'michelangelo@gmail.com', '9997-7999' ), ('Donatello' , 'donatello@gmail.com', '9777-7779' );

 * sqlite:///contatos.db
4 rows affected.

Out[4]:

[]

In [7]:

%%sql

SELECT * FROM CLIENTES

%%sql SELECT * FROM CLIENTES

 * sqlite:///contatos.db
Done.

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[7], line 1
----> 1 get_ipython().run_cell_magic('sql', '', '\nSELECT * FROM CLIENTES\n')

File c:\Python310\lib\site-packages\IPython\core\interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File c:\Python310\lib\site-packages\sql\magic.py:219, in SqlMagic.execute(self, line, cell, local_ns)
    216     return
    218 try:
--> 219     result = sql.run.run(conn, parsed["sql"], self, user_ns)
    221     if (
    222         result is not None
    223         and not isinstance(result, str)
   (...)
    226         # Instead of returning values, set variables directly in the
    227         # user's namespace. Variable names given by column names
    229         if self.autopandas:

File c:\Python310\lib\site-packages\sql\run.py:374, in run(conn, sql, config, user_namespace)
    372     if result and config.feedback:
    373         print(interpret_rowcount(result.rowcount))
--> 374 resultset = ResultSet(result, config)
    375 if config.autopandas:
    376     return resultset.DataFrame()

File c:\Python310\lib\site-packages\sql\run.py:116, in ResultSet.__init__(self, sqlaproxy, config)
    114         list.__init__(self, sqlaproxy.fetchall())
    115     self.field_names = unduplicate_field_names(self.keys)
--> 116     self.pretty = PrettyTable(self.field_names, style=prettytable.__dict__[config.style.upper()])
    117 else:
    118     list.__init__(self, [])

KeyError: 'DEFAULT'

In [8]:

%%sql

SELECT * FROM CLIENTES WHERE EMAIL = "leonardo@gmail.com"

%%sql SELECT * FROM CLIENTES WHERE EMAIL = "leonardo@gmail.com"

 * sqlite:///contatos.db
Done.

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[8], line 1
----> 1 get_ipython().run_cell_magic('sql', '', '\nSELECT * FROM CLIENTES WHERE EMAIL = "leonardo@gmail.com"\n')

File c:\Python310\lib\site-packages\IPython\core\interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File c:\Python310\lib\site-packages\sql\magic.py:219, in SqlMagic.execute(self, line, cell, local_ns)
    216     return
    218 try:
--> 219     result = sql.run.run(conn, parsed["sql"], self, user_ns)
    221     if (
    222         result is not None
    223         and not isinstance(result, str)
   (...)
    226         # Instead of returning values, set variables directly in the
    227         # user's namespace. Variable names given by column names
    229         if self.autopandas:

File c:\Python310\lib\site-packages\sql\run.py:374, in run(conn, sql, config, user_namespace)
    372     if result and config.feedback:
    373         print(interpret_rowcount(result.rowcount))
--> 374 resultset = ResultSet(result, config)
    375 if config.autopandas:
    376     return resultset.DataFrame()

File c:\Python310\lib\site-packages\sql\run.py:116, in ResultSet.__init__(self, sqlaproxy, config)
    114         list.__init__(self, sqlaproxy.fetchall())
    115     self.field_names = unduplicate_field_names(self.keys)
--> 116     self.pretty = PrettyTable(self.field_names, style=prettytable.__dict__[config.style.upper()])
    117 else:
    118     list.__init__(self, [])

KeyError: 'DEFAULT'

In [9]:

%%sql

INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) 
VALUES ('Leonardo' , 'leo@gmail.com', '1199-991199' );

%%sql INSERT INTO CLIENTES (NOME, EMAIL, TELEFONE) VALUES ('Leonardo' , 'leo@gmail.com', '1199-991199' );

 * sqlite:///contatos.db
1 rows affected.

Out[9]:

[]

In [10]:

%%sql

SELECT * FROM CLIENTES WHERE NOME = "Leonardo" AND EMAIL = 'leo@gmail.com'

%%sql SELECT * FROM CLIENTES WHERE NOME = "Leonardo" AND EMAIL = 'leo@gmail.com'

 * sqlite:///contatos.db
Done.

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[10], line 1
----> 1 get_ipython().run_cell_magic('sql', '', '\nSELECT * FROM CLIENTES WHERE NOME = "Leonardo" AND EMAIL = \'leo@gmail.com\'\n')

File c:\Python310\lib\site-packages\IPython\core\interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File c:\Python310\lib\site-packages\sql\magic.py:219, in SqlMagic.execute(self, line, cell, local_ns)
    216     return
    218 try:
--> 219     result = sql.run.run(conn, parsed["sql"], self, user_ns)
    221     if (
    222         result is not None
    223         and not isinstance(result, str)
   (...)
    226         # Instead of returning values, set variables directly in the
    227         # user's namespace. Variable names given by column names
    229         if self.autopandas:

File c:\Python310\lib\site-packages\sql\run.py:374, in run(conn, sql, config, user_namespace)
    372     if result and config.feedback:
    373         print(interpret_rowcount(result.rowcount))
--> 374 resultset = ResultSet(result, config)
    375 if config.autopandas:
    376     return resultset.DataFrame()

File c:\Python310\lib\site-packages\sql\run.py:116, in ResultSet.__init__(self, sqlaproxy, config)
    114         list.__init__(self, sqlaproxy.fetchall())
    115     self.field_names = unduplicate_field_names(self.keys)
--> 116     self.pretty = PrettyTable(self.field_names, style=prettytable.__dict__[config.style.upper()])
    117 else:
    118     list.__init__(self, [])

KeyError: 'DEFAULT'

In [11]:

%%sql

SELECT * FROM CLIENTES ORDER BY NOME

%%sql SELECT * FROM CLIENTES ORDER BY NOME

 * sqlite:///contatos.db
Done.

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[11], line 1
----> 1 get_ipython().run_cell_magic('sql', '', '\nSELECT * FROM CLIENTES ORDER BY NOME\n')

File c:\Python310\lib\site-packages\IPython\core\interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File c:\Python310\lib\site-packages\sql\magic.py:219, in SqlMagic.execute(self, line, cell, local_ns)
    216     return
    218 try:
--> 219     result = sql.run.run(conn, parsed["sql"], self, user_ns)
    221     if (
    222         result is not None
    223         and not isinstance(result, str)
   (...)
    226         # Instead of returning values, set variables directly in the
    227         # user's namespace. Variable names given by column names
    229         if self.autopandas:

File c:\Python310\lib\site-packages\sql\run.py:374, in run(conn, sql, config, user_namespace)
    372     if result and config.feedback:
    373         print(interpret_rowcount(result.rowcount))
--> 374 resultset = ResultSet(result, config)
    375 if config.autopandas:
    376     return resultset.DataFrame()

File c:\Python310\lib\site-packages\sql\run.py:116, in ResultSet.__init__(self, sqlaproxy, config)
    114         list.__init__(self, sqlaproxy.fetchall())
    115     self.field_names = unduplicate_field_names(self.keys)
--> 116     self.pretty = PrettyTable(self.field_names, style=prettytable.__dict__[config.style.upper()])
    117 else:
    118     list.__init__(self, [])

KeyError: 'DEFAULT'

In [12]:

%%sql

SELECT DISTINCT NOME FROM CLIENTES;

%%sql SELECT DISTINCT NOME FROM CLIENTES;

 * sqlite:///contatos.db
Done.

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[12], line 1
----> 1 get_ipython().run_cell_magic('sql', '', '\nSELECT DISTINCT NOME FROM CLIENTES;\n')

File c:\Python310\lib\site-packages\IPython\core\interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File c:\Python310\lib\site-packages\sql\magic.py:219, in SqlMagic.execute(self, line, cell, local_ns)
    216     return
    218 try:
--> 219     result = sql.run.run(conn, parsed["sql"], self, user_ns)
    221     if (
    222         result is not None
    223         and not isinstance(result, str)
   (...)
    226         # Instead of returning values, set variables directly in the
    227         # user's namespace. Variable names given by column names
    229         if self.autopandas:

File c:\Python310\lib\site-packages\sql\run.py:374, in run(conn, sql, config, user_namespace)
    372     if result and config.feedback:
    373         print(interpret_rowcount(result.rowcount))
--> 374 resultset = ResultSet(result, config)
    375 if config.autopandas:
    376     return resultset.DataFrame()

File c:\Python310\lib\site-packages\sql\run.py:116, in ResultSet.__init__(self, sqlaproxy, config)
    114         list.__init__(self, sqlaproxy.fetchall())
    115     self.field_names = unduplicate_field_names(self.keys)
--> 116     self.pretty = PrettyTable(self.field_names, style=prettytable.__dict__[config.style.upper()])
    117 else:
    118     list.__init__(self, [])

KeyError: 'DEFAULT'

In [32]:

%%sql
SELECT COUNT(*) FROM clientes;

%%sql SELECT COUNT(*) FROM clientes;

 * sqlite:///contatos.db
Done.

Out[32]:

COUNT(*)
5

In [33]:

%%sql

UPDATE clientes
SET nome = 'Leonardo X'
WHERE EMAIL = 'leo@gmail.com';

%%sql UPDATE clientes SET nome = 'Leonardo X' WHERE EMAIL = 'leo@gmail.com';

 * sqlite:///contatos.db
1 rows affected.

Out[33]:

[]

In [36]:

%%sql
select * from clientes;

%%sql select * from clientes;

 * sqlite:///contatos.db
Done.

Out[36]:

CLIENTE_ID	NOME	EMAIL	TELEFONE
1	Leonardo	leonardo@gmail.com	9999-9999
2	Rafael	rafael@gmail.com	9889-8998
3	Michelangelo	michelangelo@gmail.com	9997-7999
4	Donatello	donatello@gmail.com	9777-7779
5	Leonardo X	leo@gmail.com	1199-991199

In [37]:

%%sql

DELETE FROM clientes WHERE nome = 'Leonardo X';

%%sql DELETE FROM clientes WHERE nome = 'Leonardo X';

 * sqlite:///contatos.db
1 rows affected.

Out[37]:

[]

In [42]:

%%sql
select * from clientes;

%%sql select * from clientes;

 * sqlite:///contatos.db
Done.

Out[42]:

CLIENTE_ID	NOME	EMAIL	TELEFONE
1	Leonardo	leonardo@gmail.com	9999-9999
2	Rafael	rafael@gmail.com	9889-8998
3	Michelangelo	michelangelo@gmail.com	9997-7999
4	Donatello	donatello@gmail.com	9777-7779
5	Leonardo	leo@gmail.com	1999-9999

In [41]:

%%sql

INSERT INTO clientes (nome, email, telefone) 
VALUES ('Leonardo', 'leo@gmail.com', '1999-9999')

%%sql INSERT INTO clientes (nome, email, telefone) VALUES ('Leonardo', 'leo@gmail.com', '1999-9999')

 * sqlite:///contatos.db
1 rows affected.

Out[41]:

[]