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

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
(sqlite3.OperationalError) table CLIENTES already exists
[SQL: CREATE TABLE CLIENTES (
    CLIENTE_ID INTEGER PRIMARY KEY,
    NOME TEXT NOT NULL,
    EMAIL TEXT NOT NULL UNIQUE,
    TELEFONE TEXT NOT NULL UNIQUE
);]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

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 [5]:

%%sql

SELECT * FROM CLIENTES

%%sql SELECT * FROM CLIENTES

 * sqlite:///contatos.db
Done.

Out[5]:

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

In [6]:

%%sql

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

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

 * sqlite:///contatos.db
Done.

Out[6]:

CLIENTE_ID	NOME	EMAIL	TELEFONE	AGE	DURATION
1	Leonardo	leonardo@gmail.com	9999-9999	None	None

In [7]:

%%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[7]:

[]

In [8]:

%%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.

Out[8]:

CLIENTE_ID	NOME	EMAIL	TELEFONE	AGE	DURATION
5	Leonardo	leo@gmail.com	1199-991199	None	None

In [9]:

%%sql

SELECT * FROM CLIENTES ORDER BY NOME

%%sql SELECT * FROM CLIENTES ORDER BY NOME

 * sqlite:///contatos.db
Done.

Out[9]:

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

In [10]:

%%sql

SELECT DISTINCT NOME FROM CLIENTES;

%%sql SELECT DISTINCT NOME FROM CLIENTES;

 * sqlite:///contatos.db
Done.

Out[10]:

NOME
Leonardo
Rafael
Michelangelo
Donatello

In [11]:

%%sql
SELECT COUNT(*) FROM clientes;

%%sql SELECT COUNT(*) FROM clientes;

 * sqlite:///contatos.db
Done.

Out[11]:

COUNT(*)
5

In [12]:

%%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[12]:

[]

In [13]:

%%sql
select * from clientes;

%%sql select * from clientes;

 * sqlite:///contatos.db
Done.

Out[13]:

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

In [14]:

%%sql

DELETE FROM clientes WHERE nome = 'Leonardo X';

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

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

Out[14]:

[]

In [15]:

%%sql
select * from clientes;

%%sql select * from clientes;

 * sqlite:///contatos.db
Done.

Out[15]:

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

In [16]:

%%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[16]:

[]