CODEWITHSUNDEEP
python-3.xlistsqlite
12944qwerty
12944qwerty

Reputation: 2007

How to add multiple items into a column SQLite3?

I don't want to use different python packages like pickle. I also don't want to use multiple databases.

So, how do I add a list or a tuple into a column of a database?

I had a theory of adding a string that would be like '(val1, val2, val3)' and then use exec to put it into a variable but that is too far-fetched and there is definitely a better and more efficient way of doing this.

EDIT: I'll add some more information on what I'm looking for.

I want to get (and add) lists with this type of info:

{'pet':'name','type':'breed/species_of_pet', 'img':img_url, 'hunger':'100'} I want this dict to be in the pets column. Each pet can have many owners (many-to-many relationship)

Upvotes: 0

Views: 547

Answers (2)

Schwern
Schwern

Reputation: 164679

If you want to have a users table and each user can have pets. You'd first make a pets table.

create table pets (
  id integer primary key,
  name text not null,
  hunger int not null default 0
);

Then it depends on whether a pet has only one owner (known as a one-to-many relationship) or many owners (known as a many-to-many relationship).

If a pet has one owner, then add a column with the user ID to the pets table. This is a foreign key.

create table pets (
  id integer primary key,
  -- When a user is deleted, their pet's user_id will be set to null.
  user_id integer references users(id) on delete set null,
  name text not null,
  hunger int not null default 0
);

To get all the pets of one user...

select pets.*
from pets
where user_id = ?

To get the name of the owner of a pet we do a join matching each rows of pets with their owner's rows using pets.user_id and users.id.

select users.name
from users
join pets on pets.user_id = users.id
where pets.id = ?

If each pet can have many owners, a many-to-many relationship, we don't put the user_id into pets. Instead we need an extra table: a join table.

create table pet_owners (
  -- When a user or pet is deleted, delete the rows relating them.
  pet_id integer not null references pets(id) on delete cascade,
  user_id integer not null references users(id) on delete cascade
);

We declare that a user owns a pet by inserting into this table.

-- Pet 5 is owned by users 23 and 42.
insert into pet_owners (pet_id, user_id) values (5, 23), (5, 42);

To find a user's pets and their name, we query pet_owners and join with pets to get the name.

select pets.*
from pet_owners
join pets on pet_owners.pet_id = pets.id
where user_id = ?

This might seem weird and awkward, and it is, but it's why SQL databases are so powerful and fast. It's done to avoid having to do any parsing or interpretation of what's in the database. This allows the database to efficiently query data using indexes rather than having to sift through all the data. This makes even very large databases efficient.

When you query select pets.* from pets where user_id = ?, because foreign keys are indexed, SQLite does not search the entire pets table. It uses the index on user_id to jump straight to the matching records. This means the database will perform the same with 10 or 10 million pets.

Upvotes: 2

Frank
Frank

Reputation: 479

There is nothing stopping you from storing JSON or other array-like text in SQLite; it's just that it's much harder to query when you do so. SQLite does have facilities for manipulating JSON, but in general I would probably lean toward @Schwern's solution.

Upvotes: 0

Related Questions