23.2 SQLite: korzystanie z bazy danych SQLite

>>> import sqlite3
>>> conn = sqlite3.connect("datafile.db")


>>> kursor = połączenie.cursor()
>>> kursor
<sqlite3.Cursor object at 0x7fcba3868420>


>>> kursor.execute("create table people (id integer primary key, name text, count integer)")
<sqlite3.Cursor object at 0x7fef111031f0>
>>> kursor.execute("insert into people (name, count) values ('Bob', 1)")
>>> kursor.execute("insert into people (name, count) values (?, ?)", 
...                ("Jill", 15))
<sqlite3.Cursor object at 0x7fef111031f0>
>>> połączenie.commit()


>>> kursor.execute("insert into people (name, count) values (:username, :usercount)", {"username": "Joe", "usercount": 10})


>>> wynik = kursor.execute("select * from people")
>>> print(wynik.fetchall())
[(1, 'Bob', 1), (2, 'Jill', 15), (3, 'Joe', 10)]
>>> wynik = kursor.execute("select * from people where name like :name", 
...                         {"name": "bob"})
>>> print(wynik.fetchall())
[(1, 'Bob', 1)]
>>> kursor.execute("update people set count=? where name=?", (20, "Jill"))
>>> wynik = kursor.execute("select * from people")
>>> print(wynik.fetchall())
[(1, 'Bob', 1), (2, 'Jill', 20), (3, 'Joe', 10)]


>>> wynik = kursor.execute("select * from people")
>>> for wiersz in wynik:
...     print(row)
... 
(1, 'Bob', 1)
(2, 'Jill', 20)
(3, 'Joe', 10)


>>> kursor.execute("update people set count=? where name=?", (20, "Jill"))
>>> połączenie.commit()
>>> połączenie.close()

23.4.1 SQLAlchemy

>>> from sqlalchemy import create_engine, select, MetaData, Table, Column, Integer, String
>>> from sqlalchemy.orm import sessionmaker


>>> sciezkaDb  = 'datafile2.db'
>>> silnik = create_engine('sqlite:///%s' % sciezkaDb)
>>> metadane = MetaData(silnik)
>>> people  = Table('people', metadata, 
...                 Column('id', Integer, primary_key=True),
...                 Column('name', String),
...                 Column('count', Integer),
...                )
>>> Session = sessionmaker(bind=silnik)
>>> session = Session()
>>> metadata.create_all(silnik)


>>> people_ins = people.insert().values(name='Bob', count=1)
>>> str(people_ins)
'INSERT INTO people (name, count) VALUES (?, ?)'
>>> session.execute(people_ins)
<sqlalchemy.silnik.result.ResultProxy object at 0x7f126c6dd438>
>>> session.commit()


>>> session.execute(people_ins, [
...     {'name': 'Jill', 'count':15},
...     {'name': 'Joe', 'count':10}
... ])
<sqlalchemy.engine.result.ResultProxy object at 0x7f126c6dd908>
>>> session.commit()
>>> wynik = session.execute(select([people]))
>>> for wiersz in wynik:
...     print(wiersz)
... 
(1, 'Bob', 1)
(2, 'Jill', 15)
(3, 'Joe', 10)


>>> wynik = session.execute(select([people]).where(people.c.name == 'Jill'))
>>> for wiersz in wynik:
...     print(wiersz)
... 
(2, 'Jill', 15)


>>> wynik = session.execute(people.update().values(count=20).where(people.c.name == 'Jill'))
>>> session.commit()
>>> wynik = session.execute(select([people]).where(people.c.name == 'Jill'))
>>> for wiersz in wynik:
...     print(wiersz)
... 
(2, 'Jill', 20)
>>> 


>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()
>>> class People(Base):
...     __tablename__ = "people"
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     count = Column(Integer)
...
>>> wynik = session.query(People).filter_by(name='Jill')
>>> for osoba in wynik:
...     print(osoba.id, osoba.name, osoba.count)
... 
2 Jill 20


>>> nowa_osoba = People(name='Jane', count=5)
>>> session.add(nowa_osoba)
>>> session.commit()
>>> 
>>> wyniki = session.query(People).all()
>>> for osoba in wynik:
...     print(osoba.id, osoba.name, osoba.count)
... 
1 Bob 1
2 Jill 20
3 Joe 10
4 Jane 5


>>> jill = session.query(People).filter_by(name='Jill').first()
>>> jill.name
'Jill'
>>> jill.count = 22
>>> session.add(jill)
>>> session.commit()
>>> wynik = session.query(People).all()
>>> for osoba in wynik:
...     print(osoba.id, osoba.name, osoba.count)
... 
1 Bob 1
2 Jill 22
3 Joe 10
4 Jane 5


>>> jane = session.query(People).filter_by(name='Jane').first()
>>> session.delete(jane)
>>> session.commit()
>>> jane = session.query(People).filter_by(name='Jane').first()
>>> print(jane)
None

23.6 Klucz-wartość i Redis

>>> import redis
>>> r = redis.Redis(host='localhost', port=6379)


>>> r.keys()
[]
>>> r.set('klucz', 'moja_wartość')
True
>>> r.keys()
[b'klucz']
>>> v = r.get('klucz')
>>> v
b'moja_wartość'
>>> r.incr('licznik')
1
>>> r.get('licznik')
b'1'
>>> r.incr('licznik')
2
>>> r.get('licznik')
b'2'


>>> r.rpush("słowa", "jeden")
1
>>> r.rpush("słowa", "dwa")
2
>>> r.lrange("słowa", 0, -1)
[b'jeden', b'dwa']
>>> r.rpush("słowa", "trzy")
3
>>> r.lrange("słowa", 0, -1)
[b'jeden', b'dwa', b'trzy']
>>> r.llen("słowa")
3
>>> r.lpush("słowa", "zero")
4
>>> r.lrange("słowa", 0, -1)
[b'zero', b'jeden', b'dwa', b'trzy']
>>> r.lrange("słowa", 2, 2)
[b'dwa']
>>> r.lindex("słowa", 1)
b'jeden'
>>> r.lindex("słowa", 2)
b'dwa'

>>> r.setex("wygasający", "10 sekund", 10)
True
>>> r.pttl("wygasający")
7165
>>> r.pttl("wygasający")
5208
>>> r.pttl("wygasający")
1542
>>> r.pttl("wygasający")
>>>

23.7 Dokumenty w MongoDB

>>> from pymongo import MongoClient
>>> mongo = MongoClient(host='localhost', port=27017)   #A


>>> import datetime
>>> dokument = {'imie': 'Jane',
...  'wiek': 34,
...  'zainteresowania': ['Python', 'bazy danych', 'statystyka'],
...  'data_dodania': datetime.datetime.now()
}
>>> db = mongo.moje_dane     #A
>>> kolekcja = db.dokumenty   #B 
>>> kolekcja.find_one()  #C 
>>> db.collection_names(()
[]


>>> kolekcja.insert(dokument)
ObjectId('59701cc4f5ef0516e1da0dec')    #A
>>> db.collection_names()
['dokumenty']


>>> kolekcja.find_one()   #A
{'_id': ObjectId('59701cc4f5ef0516e1da0dec'), 'imie': 'Jane', 'wiek': 34, 'zainteresowania': ['Python', 'bazy danych', 'statystyka'], 'data_dodania': datetime.datetime(2017, 7, 19, 21, 59, 32, 752000)}
>>> from bson.objectid import ObjectId
>>> kolekcja.find_one({"_id":ObjectId('59701cc4f5ef0516e1da0dec')}) #B
  {'_id': ObjectId('59701cc4f5ef0516e1da0dec'), 'imie': 'Jane', 'wiek': 34, 'zainteresowania': ['Python', 'bazy danych', 'statystyka'], 'data_dodania': datetime.datetime(2017, 7, 19, 21, 59, 32, 752000)}
>>> kolekcja.update_one({"_id":ObjectId('59701cc4f5ef0516e1da0dec')}, {"$set": {"imie":"Ann"}}) #C
<pymongo.results.UpdateResult object at 0x7f4ebd601d38>
>>> kolekcja.find_one({"_id":ObjectId('59701cc4f5ef0516e1da0dec')})
  {'_id': ObjectId('59701cc4f5ef0516e1da0dec'), 'imie': 'Ann', 'wiek': 34, 'zainteresowania': ['Python', 'bazy danych', 'statystyka'], 'data_dodania': datetime.datetime(2017, 7, 19, 21, 59, 32, 752000)}
>>> kolekcja.replace_one({"_id":ObjectId('59701cc4f5ef0516e1da0dec')},
  {"imie":"Ann"})   #D
<pymongo.results.UpdateResult object at 0x7f4ebd601750>
>>> kolekcja.find_one({"_id":ObjectId('59701cc4f5ef0516e1da0dec')})
  {'_id': ObjectId('59701cc4f5ef0516e1da0dec'), 'imie': 'Ann'}
>>> kolekcja.delete_one({"_id":ObjectId('59701cc4f5ef0516e1da0dec')})     #E
<pymongo.results.DeleteResult object at 0x7f4ebd601d80>
>>> kolekcja.find_one()


>>> db.collection_names()
['dokumenty']
>>> kolekcja.drop()
>>> db.collection_names()
[]


