User:Alexander Roidl/rqlite

From Media Design: Networked & Lens-Based wiki
Jump to navigation Jump to search

Fighting with RQLITE

2 raspberries with rqlite

Install the following:

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')


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


Thanks to the developer it's working now:

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(
    Column("book", Integer, ForeignKey("")),
    Column("author", Integer, ForeignKey("")),

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

  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): = name

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

  if args.verbose:

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

  session = Session(engine)

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

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

if __name__ == '__main__':