In [94]:
%load_ext sql
%sql sqlite:///chinook.db
Out[94]:
In [95]:
a = 'Pop'
%sql select * from genres where Name = :a
Out[95]:
Можно присвоить результат запроса в переменную
In [96]:
a = %sql select * from genres
In [92]:
type(a)
Out[92]:
In [93]:
print(a)
In [97]:
import sqlite3
In [84]:
# Создаем БД в RAM
db=sqlite3.connect(':memory:')
# После окончания работы не забываем закрыть соединение
db.close()
In [85]:
# Создаем или открываем бд
db=sqlite3.connect('testdb')
# Закрываем бд
db.close()
Для того, чтобы выполнить любую операцию с базой данных необходимо создать объект cursor и передать SQL-выражение в объект cursor, чтобы вызвать его. В конце необходимо выполнить выполнить commit (заметьте, что commit выполняется для db объекта, а не cursor объекта)
In [98]:
db=sqlite3.connect('testdb')
In [99]:
# Получить cursor объекта
cursor = db.cursor()
cursor.execute('''
DROP TABLE IF EXISTS users
''');
cursor.execute('''
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT,
phone TEXT, email TEXT UNIQUE, password TEXT);
''')
db.commit()
In [100]:
cursor=db.cursor()
name1 = 'Andrew'
phone1 = '123232'
email1 = 'user@example.com'
password1 = '12345'
name2 = 'John'
phone2 = '234241'
email2 = 'john@example.com'
password2 = 'abcdef'
# Insert user 1
cursor.execute('''INSERT INTO users(name, phone, email, password)
VALUES(?,?,?,?)''', (name1, phone1, email1, password1))
print('First user inserted')
# Insert user 2
cursor.execute('''INSERT INTO users(name, phone, email, password)
VALUES(?,?,?,?)''', (name2, phone2, email2, password2))
print('Second user inserted')
db.commit()
Значения переменных python подставляются через кортеж. Другой способ - через словарь, используя ':'
In [101]:
name3 = 'Nikita'
phone3 = '323232'
email3 = 'nikita@example.com'
password3 = '123'
cursor = db.cursor()
cursor.execute('''INSERT INTO users(name, phone, email, password)
VALUES(:name, :phone, :email, :password)''',
{'name':name3, 'phone':phone3, 'email':email3, 'password':password3})
print('Third user inserted')
db.commit()
In [103]:
name3 = 'Nikita'
phone3 = '323232'
email3 = 'nikita@example.com'
password3 = '123'
cursor = db.cursor()
cursor.execute('''INSERT INTO users(name, phone, email, password)
VALUES(:name3, :phone3, :email3, :password3)''')
print('Third user inserted')
db.commit()
Если вы хотите вставить нескольо пользователей в таблицу, используйте executemany и список из кортежей
In [104]:
name4 = 'Ann'
phone4 = '490904'
email4 = 'ann@example.com'
password4 = '345'
name5 = 'Jane'
phone5 = '809908'
email5 = 'jane@example.com'
password5 = '785'
users = [(name4, phone4, email4, password4),
(name5, phone5, email5, password5)]
cursor.executemany('''INSERT INTO users(name, phone, email, password) VALUES (?,?,?,?)''', users)
db.commit()
Если вам требуется получить ид строки, которую вы только что добавили, используйте lastrowid
In [46]:
id = cursor.lastrowid
print('Last row id: %d' % id)
In [47]:
cursor.execute('''SELECT name, email, phone FROM users''')
user1 = cursor.fetchone() # получить одну строку
print(user1[0])
all_rows = cursor.fetchall()
for row in all_rows:
# row[0] возращает первый столбец - name, row[1] - email, row[2] - phone
print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))
Объект cursor работает как итератор, вызывая fetchall() автоматически
In [48]:
cursor.execute('''SELECT name, email, phone FROM users''')
for row in cursor:
print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))
Чтобы получить данные с условиями, используйте '?'
In [51]:
user_id=3
cursor.execute('''SELECT name, email, phone FROM users WHERE id=?''', (user_id,))
user=cursor.fetchone()
print (user[0], user[1], user[2])
In [55]:
# Обновить пользователя с id = 1
newphone = '77777'
userid = 1
cursor.execute('''UPDATE users SET phone = ? WHERE id = ?''', (newphone, userid))
# Удалить пользователя с id = 2
delete_userid = 2
cursor.execute('''DELETE FROM users WHERE id = ?''', (delete_userid,))
db.commit()
In [56]:
cursor.execute('''UPDATE users SET phone = ? WHERE id = ? ''', (newphone, userid))
db.commit()
Или rollback для отмены изменений
In [58]:
cursor.execute('''UPDATE users SET phone = ? WHERE id = ?''', (newphone, userid))
db.rollback()
Помните, что всегда требуется сохранить изменения. Если вы закроете соединение или соединение будет потеряно, то ваши изменения не будут внесены
In [60]:
db.close()
In [105]:
import sqlite3
try:
db=sqlite3.connect('testdb')
cursor=db.cursor()
cursor.execute('''CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT
email TEXT unique, password TEXT)''')
db.commit()
except Exception as e:
db.rollback()
print('we are here')
raise e
finally:
db.close()
В этом примере мы используем try/except/finally для того, чтобы "поймать" исключение в коде. Служебное слово finally - очень важно, потому что благодаря ему коннект к бд закрывается корректно. Более подробно здесь.
Используя except as Exception, мы ловим все исключения. Обычно в production коде необходимо "ловить" определенное исключение. Ссылка
Можно использовать объект Connection для автоматического commit'а и rollback'а
In [106]:
name1 = 'Andres'
phone1 = '333658'
email1 = 'user@example.com'
password1 = '12345'
try:
db=sqlite3.connect('testdb')
with db:
db.execute('''INSERT INTO users(name, phone, email, password)
VALUES(?,?,?,?)''', (name1, phone1, email1, password1))
except sqlite3.IntegrityError:
print('Record already exists')
finally:
db.close()
В пример выше, если insert вызывает исключение, для транзакции будет совершен откат и сообщение будет написано, иначе транзакция будет выполнена. Заметьте, что в данном случае мы вызываем execute на db объект.
In [77]:
%%sql
select company
FROM invoices
join customers
ON invoices.customerid = customers.customerid
WHERE customers.company <> 'None'
group by customers.customerid, customers.company
having count(*)
in
(
select min(cnt) from
(
select count(*) as cnt
FROM invoices
group by customerid
) A
UNION ALL
select max(cnt) from
(
select count(*) as cnt
FROM invoices
group by customerid
) A
)
Out[77]:
In [ ]: