Template for MySQL Table Creation - Python

Question

I am creating one table per user in my database and later storing data specific to that user. Since I have 100+ users, I was looking to automate the table creation process in my Python code.

Much like how I can automate a row insertion in a table, I tried to automate table insertion.
Row insertion code:

PAYLOAD_TEMPLATE = (
    "INSERT INTO metadata "
    "(to_date, customer_name, subdomain, internal_users)"
    "VALUES (%s, %s, %s, %s)"
)

How I use it:

connection = mysql.connector.connect(**config)
cursor = connection.cursor()
# Opening csv table to feed data
with open('/csv-table-path', 'r') as weeklyInsight:
    reader = csv.DictReader(weeklyInsight)
    for dataDict in reader:
        # Changing date to %m/%d/%Y format
        to_date = dataDict['To'][:5] + "20" + dataDict['To'][5:]
        payload_data = (
            datetime.strptime(to_date, '%m/%d/%Y'),
            dataDict['CustomerName'],
            dataDict['Subdomain'],
            dataDict['InternalUsers']
        )
        cursor.execute(PAYLOAD_TEMPLATE, payload_data)

How can I create a 'TABLE_TEMPLATE' that can be executed in a similar way to create a table?

I wish to create it such that I can execute the template code from my cursor after replacing certain fields with others.

TABLE_TEMPLATE = (
    "  CREATE TABLE '{customer_name}' (" # Change customer_name for new table 
    "'To' DATE NOT NULL,"
    "'Users' INT(11) NOT NULL,"
    "'Valid' VARCHAR(3) NOT NULL"
    ") ENGINE=InnoDB"
)

I believe I found a solution using the format() function in Python. Finally! It works! — Vaibhav Bajaj
– Vaibhav Bajaj, Commented Jun 27, 2017 at 0:01
Unless you have a very good reason to create a table for each user I suggest changing your design. It is usually much easier and cleaner to simply create a single users table. — Chris
– Chris, Commented Jun 27, 2017 at 0:41
I hope whatever value you're passing into format() doesn't come from the users themselves. If it does you are vulnerable to SQL injection. — Chris
– Chris, Commented Jun 27, 2017 at 0:43
@Chris I am hoping to store the weekly numbers of InternalUsers as separate rows in each table for each company/user and later use this data to graph out the increase/decrease of InternalUsers in a nice UI. All data coming is coming from offline pre-created Excel sheets. This is all server side. I wanted to store lists of the InternalUser numbers in each row as though a row was a user. However, I cannot seem to create and later add to a list in mySQL. — Vaibhav Bajaj
– Vaibhav Bajaj, Commented Jun 27, 2017 at 4:05

Chris · Accepted Answer · 2017-06-27 15:01:58Z

1

There is no technical¹ need to create a separate table for each client. It is simpler and cleaner to have a single table, e.g.

-- A simple users table; you probably already have something like this
create table users (
  id integer not null auto_increment,
  name varchar(50),

  primary key (id)
);

create table weekly_numbers (
  id integer not null auto_increment,

  -- By referring to the id column of our users table we link each
  -- row with a user
  user_id integer references users(id),

  `date` date not null,
  user_count integer(11) not null,

  primary key (id)
);

Let's add some sample data:

insert into users (id, name)
values (1, 'Kirk'),
  (2, 'Picard');

insert into weekly_numbers (user_id, `date`, user_count)
values (1, '2017-06-13', 5),
  (1, '2017-06-20', 7),
  (2, '2017-06-13', 3),
  (1, '2017-06-27', 10),
  (2, '2017-06-27', 9),
  (2, '2017-06-20', 12);

Now let's look at Captain Kirk's numbers:

select `date`, user_count
from weekly_numbers

-- By filtering on user_id we can see one user's numbers
where user_id = 1
order by `date` asc;

¹There may be business reasons to keep your users' data separate. A common use case would be isolating your clients' data, but in that case a separate database per client seems like a better fit.

answered Jun 27, 2017 at 15:01

Chris

139k139 gold badges316 silver badges293 bronze badges

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

1 Comment

Vaibhav Bajaj Over a year ago

Just recently, I discussed my use-case with my senior. He gave precisely this idea! You read minds, my dear friend.

Collectives™ on Stack Overflow

Template for MySQL Table Creation - Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related