命令不同步，您现在无法运行此命令 [英] Commands out of sync you can't run this command now

问题描述

我正在尝试使用mysqldb创建一些表.

I am trying to create some tables using mysqldb.

问题在于，当执行python脚本db.py时，mysql会引发错误:

The issue is that when executing the python script db.py mysql throws the error:

_mysql_exceptions.ProgrammingError:(2014，命令不同步；您现在不能运行此命令")

_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

db.py:

import MySQLdb
import MySQLdb.cursors

def init_db():
    db_conn = get_db_conn()
    cursor = db_conn.cursor()

    with open("tables.sql", 'r') as f:
        cursor.execute(f.read())

def get_db_conn():
    return MySQLdb.connect(
        host="localhost",
        user="root",
        passwd="secretcat",
        db="uptrender",
        cursorclass=MySQLdb.cursors.DictCursor
    )

init_db() 

tables.sql:

tables.sql:

DROP TABLE IF EXISTS Test2;
DROP TABLE IF EXISTS Test;

CREATE TABLE Test (
    id INT NOT NULL
);

CREATE TABLE Test2 (
    id INT NOT NULL,
    FOREIGN KEY(id) REFERENCES Test(id)
);

根据 mysql文档以错误的顺序调用客户端函数时，将给出此错误.看我用的那些(我认为只有3个)，它们看起来是正确的顺序.首先连接到数据库，获取游标，最后执行查询以创建表.这是错误的顺序吗?在连接到数据库之前执行查询似乎不合逻辑...

According to the mysql docs this error is given when client functions are called in the wrong order. Looking at the ones i use (i only have 3 i think) they look to be in the correct order. First connecting to the database, getting the cursor and finally excecuting the query to create tables. Is this the wrong order? It would not seem logical to do the query before connecting to the database...

我在用表填充数据库后尝试关闭连接，但没有区别.

I tried closing the connection after populating the database with tables but it makes no difference.

此外，我试图完全删除数据库并重新创建它，但mysql仍然抛出相同的错误.

Furthermore i tried to drop the database completely and re-create it but mysql still throws the same error.

我发现如果删除tables.sql顶部的两个DROP TABLES IF EXISTS tablename语句，我不会得到错误.但是似乎只有第一个表(测试)已创建(在mysql命令行客户端中使用SHOW TABLES;进行验证)！到底发生了什么事?

I found that if i remove the two DROP TABLES IF EXISTS tablename-statements in the top of tables.sql i do not get the error. But only the first table (test) seems to have been created (using SHOW TABLES; in the mysql command line client to verify this)! What the heck is going on there?

所以我进一步隔离了问题，它与flask没有关系.

So i isolated the problem further, it had nothing to do with flask.

推荐答案

好吧，我发现我必须一个一个地执行语句.我现在这样做:

Okay, i figured out that i have to execute the statements one by one. I now do this:

from flask import current_app, g

import MySQLdb
import MySQLdb.cursors
import re

def init_db():
    db_conn = get_db_conn()
    cursor = db_conn.cursor()
    f = current_app.open_resource("tables.sql")
    tables = f.read().decode('utf-8').split(';')
    f.close()
    for table in tables:
        table = re.sub('[\n\r]', '', table)
        if len(table) > 0:
            cursor.execute(table)

这篇关于命令不同步，您现在无法运行此命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！