User:Alexander Roidl/rqlite

Fighting with RQLITE

2 raspberries with rqlite

Install the following:

• https://github.com/rqlite/rqlite/releases
• https://github.com/rqlite/pyrqlite
• https://github.com/rqlite/sqlalchemy-rqlite

3 lines to change from SQLite to RQLite / all SQLalchemy commands stay the same (awesome)

from sqlalchemy.dialects import registry
registry.register("rqlite.pyrqlite", "sqlalchemy_rqlite.pyrqlite", "dialect")
app.config['SQLALCHEMY_DATABASE_URI'] = 'rqlite+pyrqlite://localhost:4001/'

changing back to SQLite is as simple:

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'mydatabase.db')

ERROR

sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'books_authors' expected to delete 1 row(s); Only 0 were matched.

UPDATE:

Thanks to the developer it's working now:

https://github.com/rqlite/sqlalchemy-rqlite/issues/6

A Simple RQLite setup

Check the database

#!/usr/bin/env python

import argparse
import functools
import logging
import pyrqlite.dbapi2 as dbapi2
#import sqlite3 as dbapi2

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm import Session
from sqlalchemy import Table, Column, Integer, String, ForeignKey, Sequence, create_engine
from sqlalchemy.dialects import registry
registry.register("rqlite.pyrqlite", "sqlalchemy_rqlite.pyrqlite", "dialect")

Base = declarative_base()

books_authors = Table(
    "books_authors",
    Base.metadata,
    Column("book", Integer, ForeignKey("books.id")),
    Column("author", Integer, ForeignKey("authors.id")),
)

class Book(Base):
  __tablename__ = 'books'
  id = Column(Integer, primary_key=True)
  title = Column("title", String(50), nullable=False)
  authors = relationship(
      "Author",
      backref="books",
      secondary=books_authors
  )

  def __init__(self, title):
      self.title = title

class Author(Base):
    __tablename__ = "authors"
    id = Column("id", Integer, Sequence("authors_id_seq"), primary_key=True)
    name = Column("name", String(50), nullable=False)

    def __init__(self, title):
      self.name = name

def main():
  parser = argparse.ArgumentParser()
  parser.add_argument('-v', '--verbose', action='store_true', default=False)
  args = parser.parse_args()

  if args.verbose:
    logging.basicConfig()
    logging.getLogger().setLevel(logging.DEBUG)

  engine = create_engine('rqlite+pyrqlite://localhost:4001/', echo=args.verbose)

  Base.metadata.create_all(engine)
  session = Session(engine)

  #insert
  # for i in range(1):
  #   book = Book("book about dbs2")
  #   author = Author("footurist")
  #   book.authors.append(author)
  #   session.add(book)
  # session.commit()

  #delete
  desired_book = session.query(Book).filter_by(id=3).delete()
  #desired_author = session.query(Author).filter_by(id=2).first()
  #desired_book.authors.clear()
  #desired_book.authors.append(desired_author)
  session.commit()

if __name__ == '__main__':
  main()