Python3/SQLite3 | How to create multiple tables from a list or list of tuples?

Question

I have a list of tuples like so:

>>> all_names = c.execute("""select name from fb_friends""")
>>> for name in all_names:
...     print(name)
('Jody Ann Elizabeth Lill',)
('Georgia Gee Smith',)
...(282 more)...
('Josh Firth',)
('Danny Hallas',)

And I want to create a table for each individual person. First I need to replace all spaces with an underscore in order to be SQLite3 table names, so I do that like so:

>>> all_names = c.execute("""select name from fb_friends""")
>>> for name in all_names:
...     friends_name = name[0].replace(" ", "_")
...     print(friends_name)
Jody_Ann_Elizabeth_Lill
Georgia_Gee_Smith
...(282 more)...
Josh_Firth
Danny_Hallas

So if I understand correctly I now have a list, not a list of tuples..? Which should be simple to create all my tables from this list like so:

>>> all_names = c.execute("""select name from fb_friends""")
>>> for name in all_names:
...     friends_name = name[0].replace(" ", "_")
...     c.execute("""create table {0} (`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `work` TEXT NOT NULL, `education` TEXT, `current_city` TEXT, `phone_number` TEXT, `dob` TEXT, `gender` TEXT, `sexual_orientation` TEXT, `religion` TEXT, `relationship_status` TEXT, `about_me` TEXT )""".format(friends_name))

But all it does is create a table from the first name in the list, I would have thought the for loop would iterate over the list of names and create a table for each one, which is what I want, can people please advise me on a few things:

1. Is using the .replace method the best way to get the underscores in the names? If no then what is?
2. Why is the for loop not iterating over each name to create the tables? And how do I make it do that?
3. Are my methods correct at all? If not then what methods would do it better?

Answer 1

While your immediate issue is resolved likely due to the raw cursor not retrieving all records from database whereas cursor.fetchall() or list(cursor) imports all rows to client side (Python), a slight memory footprint but may ensure rows import in script, I would be remiss to not encourage best practices in database design.

As mentioned in comments, reconsider this approach and normalize your entire database model for one-to-many or many-to-many relationships between tables as relational databases are intended to do. This way you avoid the overhead, maintenance, and querying challenges of 280+ identically structured tables.

From your explanation, you sound like you need a distinct friends table with one-to-many links with workplaces and posts table and any others, all connected to a friendid as foreign keys. Ultimately, you would create tables only once and populate data with append queries:

-- CREATE TABLES
CREATE TABLE friends (
     `friendid` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     `first_name` TEXT, last_name TEXT,
     `work` TEXT, `education` TEXT, 
     `current_city` TEXT, `phone_number` TEXT, 
     `dob` TEXT, `gender` TEXT, `sexual_orientation` TEXT, 
     `religion` TEXT, `relationship_status` TEXT, `about_me` TEXT);

CREATE TABLE workplaces (
     `workplaceid` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     `friendid` INTEGER,
     `workplace` TEXT NOT NULL, 
     `city` TEXT, `state` TEXT);

CREATE TABLE posts (
     `postid` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     `friendid` INTEGER,
     `text` TEXT NOT NULL,
     `postdate` TEXT, `postlocation` TEXT);

-- POPULATE TABLES (adjust to actual tables/fields)  
INSERT INTO friends (first_name, last_name)
SELECT SUBSTR(`name`, 1, INSTR(`name`, " ")) AS first_name,
       SUBSTR(`name`, INSTR(`name`, " ")+1) AS last_name,
FROM fb_friends;

INSERT INTO workplaces (friendid, work, city)
SELECT f.friendid, w.work, w.city 
FROM fb_work w INNER JOIN friends f ON w.name = f.first_name & ' ' & f.last_name;

INSERT INTO posts (friendid, `text`, postdate)
SELECT f.friendid, p.post_text, p.post_date 
FROM fb_posts p INNER JOIN friends f ON p.name = f.first_name & ' ' & f.last_name;

Answer 2

So after continually playing around with various methods I managed to figure out how to do this. In order to get the the code to work all that had to be done was to convert it to a list, like so:

>>> all_names = c.execute("""select name from fb_friends""")
>>> names = list(all_names) # I just added this line and changed the variable names below
>>> for name in names:
...     friends_name = name[0].replace(" ", "_")
...     c.execute("""create table {0} (`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `work` TEXT NOT NULL, `education` TEXT, `current_city` TEXT, `phone_number` TEXT, `dob` TEXT, `gender` TEXT, `sexual_orientation` TEXT, `religion` TEXT, `relationship_status` TEXT, `about_me` TEXT )""".format(name))

Answer 3

please try out this solution:

staff = ['a', 'b', 'c']


for name in staff:
    c.execute("CREATE TABLE IF NOT EXISTS %s (name TEXT);" % (name))