Chatterbot: Postgres storage adapter

[Dulce06]

Hi,

Has anyone come up with a storage adapter for Postgres.

I would like to have postgres storage adapter rather than SQL and Mongodb. But, I am really struggling to create one. Can someone please help me with this.

If all good, I will prepare the documentation for this.

Many thanks in advance.

[Dulce06]

Hi all,

I have successfully created storage adapter for postgres. I can now save the traninig data and conversations on postgres.

The only issue is, it doesn't store the conversation in an order and some of the statements are missing.
Glad that I am able to connect to the db and save something finally.

Will try to fix this issue soon and will update.

[gunthercox]

Hi @Dulce06, if it helps at all, ChatterBot's SQL storage adapter uses the SQL Alchemy library. SQL Alchemy supports Postgres so you might not need to build your own.

https://docs.sqlalchemy.org/en/latest/dialects/#included-dialects

Please let me know if you run into any issues as I do not currently have the resources to run tests for all databases supported by SQL Alchemy.

[Dulce06]

Hi @gunthercox

Thanks for the message.

I am able to connect to the postgres. But, when I interact with my bot , not all the messages are saved in my DB. It skips couple of them/ any single line in between and they are out of sync ( results data stored in the wrong column) .

Any suggestion to fix this?

Many thanks

[gunthercox]

@Dulce06 can you tell me what version of ChatterBot you are using? Also, an example of the data that is appearing in the wrong column would be useful.

[Dulce06]

Hi @gunthercox

I am using chatterbot version 0.8.7

Bot conversation:

Bot : Hello, my name is Bani. How can I help you?
Me : hi
Bot : How are you doing?
Me : I am good
Bot : where are you located
Me : everywhere
Bot : I'm on IRC. I never have time to feel lonely!
Me : what do you eat
Bot : Do you hate?
Me : no
Bot : Should I be? Did something happen?
Me : Nothing
Bot : Or something
Me : Go and sleep
Bot : Bots never lie

This is how the data is stored

                      text                        created at                                            filter                 statement_text

"840" "I am good" "2019-01-16 15:00:10" "1" "everywhere"
"841" "where are you located" "2019-01-16 15:01:02" "1" "what do you eat"
"842" "everywhere" "2019-01-16 15:01:07" "1" "no"
"843" "what do you eat" "2019-01-16 15:01:14" "1" "Nothing"
"844" "what do you eat" "2019-01-16 15:01:23" "1" "Go and sleep"

It doesn't have all the responses/statements, the data is repeated twice, and the data is also stored in the other columns (not in the expected ones).

As I believed, text column contains the chat responses and statement_text column has the user_input.
How can this be resolved.

Many thanks

[gunthercox]

@Dulce06 I believe this may have been fixed in the 1.0 release. Would you be willing to test out ChatterBot version 1.0 to verify this?

[Dulce06]

Sure , will do and let you know. @gunthercox

[Dulce06]

Hi @gunthercox

I have created a new database on postgres.

By default, I have statement, tag and tag_association.
I want to limit statement table to store only the training data.

I would like to create a new table "Bani" and save all the bot conversations here.

I have created a table by adding it on models.py but not sure where to edit the code so that all the conversations are stored into "Bani"

Could you please help me with this.
Many thanks

[vkosuri]

@Dulce06 did you created migrations for your project after adding tables? https://chatterbot.readthedocs.io/en/stable/django/index.html#migrations

python manage.py migrate django_chatterbot

[Dulce06]

@vkosuri

I have not done this.

Not sure as this is not django project and does not have manage.py
I have just created a plain project on pycharm and started working on it.

Could you please let me know if this is a wrong approach.

Many thanks

[vkosuri]

If you are updating models.py under ext directory, then you are using Django project, If don't like use Django, you have to write a new storage adapter like this https://github.com/gunthercox/ChatterBot/blob/master/chatterbot/storage/sql_storage.py

[Dulce06]

Hi @vkosuri

Thanks for the response.

I have copied sql_storage.py and then modified it so as to connect to the postgres database. It was successfully connected to the postgres database.
Once I run the script, the training data is stored in the postgres as expected

But, I want to store the conversations in a different table on the same database. Which file to modify so that this conversation is stored in other new table.

Thanks in advance.

[vkosuri]

If you look into sql_storage_adapter, https://github.com/gunthercox/ChatterBot/blob/90fb86577d2201eb66f4c8b86902cee5ac394d13/chatterbot/storage/sql_storage.py#L176

You have to change all the methods such as create, update, create_many and many places, instead, I would suggest you please remain same Statement and Response tables, You could this SQLStorageAdapter class and call superclass methods inside overridden methods, and insert Conversation data into your expected table.

[Dulce06]

@vkosuri

Thank you very much.
I will try this and keep you updated :)

[Dulce06]

HI @vkosuri and @gunthercox

I have tried this approach but didn't work for me. I had to change different files which has reference to other files.
I was able to create a new table from the code but can't save the conversations in that table.
Any suggestions of what exact files and functions that I need to modify to make it work.

I appreciate your time and thanks in advance.

[vkosuri]

Any error stacktrace

[gunthercox]

Did you try using a Postgres database url with the existing SQL storage adapter?

chatbot = ChatBot(
    'Example Bot',
    storage_adapter='chatterbot.storage.SQLStorageAdapter',
    database_uri='postgres://admin:donotusethispassword@localhost:15813/my-database-name'

[RIBA-test]

While trying to use sql storage adapter i am getting " psycopg2.DataError: value too long for type character varying(255)" while training corpus data

[Dulce06]

@gunthercox , @vkosuri

Yes, have used the postgres URL. It's connected and has the tables by default (as expected).
The only issue is to save the conversations in the new table that I created.

I tried changing files. It executes without errors but do not save any conversations in the new table that I created.

[gunthercox]

@Dulce06 Unfortunately I can't guide you through the process of creating additional tables to use with your chat bot. It sounds like a lot of work for you to make this customization. You might also encounter some problems as a result of the change you want to make, especially considering that the rest of the ChatterBot code base may rely on "conversations" to be an attribute on the statement table, and not a table of it's own.

If you have a particular use-case, I would be be happy to try to make a suggestion on how to use the existing conversation field to achieve the functionality you are looking for.

[Dulce06]

Hi @gunthercox

Thanks for the response.

I am not separating the conversation attribute on the Statement table.

I have created a new database which has 4 columns.
1) S No
2) Bot's message
3) User's message
4) Timestamp

I want to save what bot is talking to the user and what user is responding to the bot with timestamp in this new table.

Which command is responsible for "creating a statement if not existing in the Statement table" in the Sql storage adapter. Could you please point me there so that I can change my adapter accordingly.

Also, which variable is holding user's question and bot's response. Could you please point me there.

Many thanks and I really appreciate your help so far.

[gunthercox]

Sure, the method the is responsible for creating a statement in the SQL storage adapter is called create. In this method, you will find a variable called statement. This is the statement that this method will end up saving. The bot's response is generated later in the get_response method of the ChatBot class.

I hope this information helps you in your coding endeavors.

[Dulce06]

@gunthercox
Perfect :)
Thank you very much.
I will try this. Will keep you updated.

[pylobot]

Maybe we can close this issue since chatterbot has a storage adapter for this already?

[Dulce06]

@pylobot
Chatterbot has sql and mongodb storage adapters but not postgres.

[pylobot]

SQL storage is just the name of the adapter. It works with many types of SQL database.

Postgres is a type of SQL database and it is supported by the SQL storage adapter.

[Dulce06]

Yes, I know. But that didn't work. So raised an issue.

[pylobot]

If I may ask, what error was encountered?

[Dulce06]

Can you please go through this thread. That explains all.

[Dulce06]

Hi @gunthercox and @vkosuri

I have tried this but no progress. I am stuck with other work and giving up for now. May be I will try later once I am free. Not sure if we can close this or wait. Please suggest.

Thanks

[gunthercox]

I'm going to suggest closing this ticket off for now. There aren't any changes described here that I can make, or act upon.

[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.