Chatterbot: Create SQLite storage adapter

[gunthercox]

SQLite will provide better performance than the JsonFileAdapter while still allowing for an easy setup (MongoDB and other databases require additional packages to be installed).

[navyad]

@gunthercox I would like to work in this, if somebody is not already working on it.

[gunthercox]

@navyad Thank you! I don't believe that anyone is working on this.

[navyad]

@gunthercox Thanks.

[Nixellion]

@navyad Any news? I see the sqlite fork, but it still says wip. Any eta? :) Just curious.

[navyad]

@Nixellion , development is going on here. @davizucon has taken over this issue.

[Nixellion]

thanks for info

[davizucon]

Hi @Nixellion, thanks for asking, by the way, the storage adapter using SQLalchemy. There is a few tests not passing, when everything ok I'll make a PR, for beta. Feel free to ask and suggestions.

[rajasimon]

Nice to see this integrated ASAP

[rajasimon]

@davizucon I see "All testes passing" commit message. What about pull request?

[davizucon]

Hi @rajasimon, Yes but I don't fell confidence enough to delivery. I`ll need to do some more tests against others databases (SQLite (default and done) , MySQL (in progress) and PostgreSQL(in progress)) to see if there is any issue.

By the way, what you think:

All parameters are optional, except storage_adapter

chatbot = ChatBot( ....

• storage_adapter="chatterbot.storage.SQLAlchemyDatabaseAdapter",
• database_uri="sqlite:///database_test.db", # use database_uri or database, database_uri can be especified to choose database driver
• database="database_test", # use for sqlite database. Ignored if database_uri especified .
• read_only=False, # Readonly database, Default: False
• drop_create=True # To recreate database every start read_olny must be False, Default: False

[rajasimon]

Hi @davizucon, Thanks for the reply and I tried your code. Anyway here is my thought on the first run...

• Can you make sure chatterbot.storage.SQLAlchemyDatabaseAdapter
• And in sqlalchemy.py why you hideout (34 and 52 line) ? Also I don't think you don't need primary_key=True in text. Just id = Column(Integer, primary_key=True) is enough.

And I don't see any other running issues now. If anything comes up I will let you know.

[davizucon]

Hi @rajasimon , thanks for addition !

I'll check out primary_key and run the tests with the change.

Ok ok, I'll make a PR for gunthercox/master, I'll suggest for this feature remains in alpha stage, until we make appropriate documentation and further tests.

[gunthercox]

@davizucon I'd be happy to bring in the SQLite adapter for an alpha release. I have been getting a number of complaints about the performance of the JSON file adapter lately and I think I'm going to make plans to remove it completely (eventually) in favor of the SQLite adapter once it is stable.

[davizucon]

Hi @gunthercox , I see. Sorry for delay to delivery, tough days.

I have based these tables on the actual JSON structure and I believe in the near future, maybe in the next PR, I will change table design. So keep training data somewhere that you can enter again.
added: SQLite is easy to enter data because ANSI SQL is kind of standard and easily manipulated

By the way, there is another approach and can be developed in parallel with SQLite, tinydb,
"is a lightweight document oriented database optimized", imo have more glue with actual JSON. What you think @gunthercox, @rajasimon, If you think that can bring performance and search flexibility (that REALLY matters), let's start another issue/thread, them we can contribute.

[a-elhaddad]

there is any way to see exemple using sqlalchemy ?

[gunthercox]

Hi @addelll, you can use this storage adapter the same way that the others are used.

For example,

chatbot = ChatBot(
    "My ChatterBot",
    storage_adapter="chatterbot.storage.SQLAlchemyDatabaseAdapter"
)

Is this what you are looking for as an example?

[gunthercox]

Thank you to everyone who has contributed to the creation of the SQL Storage adapter!

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.