0

I need to create multiple tables in MySQL, and I've written it in Python. I thought to put it in one variable like this:

cursor = conn.cursor()

query = """
CREATE TABLE `A` (
  `a_col1` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `a_col2` datetime(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `B` (
  `b_col1` varchar(20) NOT NULL,
  `b_col2` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `C` (
  `c_col1` varchar(50) DEFAULT NULL,
  `c_col2` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
cursor.execute(query)

But I am encountering an error which is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE B... at line 6'

Am I missing something here? Also I can hear some suggestions for a better approach.

Thanks!

Jared Dominic Caraan
  • 23
  • 6

1 Answers1

2

You just have to use cursor.executemany instead

see this old post Python + MySQLdb executemany and the documentation https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

Camille Tolsa
  • 261
  • 6
  • 13