Multiple CREATE statements in one variable in Python MySQL

Question

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!

it should be a_col1 varchar(40) CHARACTER SET utf8mb4 or utf8mb3 — nbk
– nbk, Commented Mar 23, 2020 at 16:40

Camille Tolsa · Accepted Answer · 2020-03-23 16:40:54Z

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

answered Mar 23, 2020 at 16:40

Camille Tolsa

2616 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Multiple CREATE statements in one variable in Python MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related