python - MySQL 表创建模板 - Python

Question

我正在数据库中为每个用户创建一个表，然后存储特定于该用户的数据。由于我有 100 多个用户，因此我希望在 Python 代码中自动执行表创建过程。

就像我如何在表中自动插入行一样，我尝试自动插入表。
行插入代码:

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

我如何使用它:

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)

如何创建一个可以以与创建表类似的方式执行的'TABLE_TEMPLATE'？

我希望创建它，以便在用其他字段替换某些字段后可以从我的光标执行模板代码。

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"
)

Answer 1

技术上不需要为每个客户端创建单独的表。使用单个表更简单、更干净，例如

-- 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)
);

让我们添加一些示例数据:

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);

现在让我们看看柯克船长的数字:

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;

``可能出于业务原因而需要将用户数据分开。一个常见的用例是隔离客户的数据，但在这种情况下，每个客户都有一个单独的数据库似乎更合适。