1

As long as I know, there are only five data types that can be set to columns in a sqlite3 table. They are:

  • null means no data.
  • integer means a whole number.
  • real means a float.
  • text means any string.
  • blob a binary data field in which you can store files, documents, images.

But currently, I have a list called self. inventory in my code, that gets items added into it occasionally when users' do something specific. That is not the issue. My problem is that what data type should I assign to the list that I want to store in the table? Or is there any other method I can use to store the values of the table into the db. Currently, here is my connection, cursor and table execution:

connection = sqlite3.connect('db_of_game.db')
cursor = connection.cursor()

cursor.execute(
    'CREATE TABLE user_data(user_name text primary key, money integer, inventory <What data type to use here?>, deposited integer, allowed_deposit integer)'
    )

connection.commit()
connection.close()
Improve this question
6
  • You could join the list items into a comma separated string ("item1,item2,item3") and use the text type, then use .split(",") to get the list back when reading from the database. Commented Apr 10, 2021 at 20:17
  • Do I create a variable containing the comma separated string and add it into the table? Is it the best approach? How do I bring it back into the list when I want? Commented Apr 10, 2021 at 20:19
  • Yes, it would be a good idea to create a variable. This is probably the best approach, and certainly the simplest. To bring it back to a list, use list.split(","). This will turn the comma separated string into a python list. Commented Apr 10, 2021 at 20:21
  • Never store comma separated strings in a table. read this: stackoverflow.com/questions/3653462/… Commented Apr 10, 2021 at 20:21
  • So what am I supposed to do? @forpas Commented Apr 10, 2021 at 20:26

1 Answer 1

Reset to default
3

Assuming each item can only belong to a single user, you'd use a one-to-many pattern. Many items, one user. Items have their own table and they refer to their user.

create items (
  id integer primary key,
  name text not null,
  user_name text not null references user_data(user_name)
)

(Note: Using a username as a primary key is to be avoided. Usernames change. Primary keys cannot change. They also require more storage and comparison time. Instead, use a simple integer. In SQLite integer primary key works.)

Then to get all a user's items...

select items.name
from items
where user_name = ?

If each item can belong to many users, that is a many-to-many relationship and you need a join table to link users to items.

create items (
  id integer primary key,
  name text not null
)

create inventory (
  item_id integer not null references items(id),
  user_name text not null references user_data(user_name)
)

And to get a user's inventory you check inventory to get the item IDs and join with items to get the item name.

select items.name
from items
join inventory on items.id = inventory.item_id
where inventory.user_name = ?

This might seem convoluted, but this is how a relational database works. By setting up relationships between items. It takes a bit to wrap your head around, but it's worth it. It makes searching very fast. If you used a comma separated list and want to find the users with a certain item, you need to look at every user and parse their list. Now you just query the items table. If items.name is indexed it will not have to search the whole table.

select *
from items
where item.name like ?

For more...

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.