python - 尝试使用 Python 将数据插入 MySQL 表时出现编程错误

Question

虽然我对 SQL 很陌生，但我已经使用 python 来构建数据库，但现在我陷入了困境。

简单地说，我有一个包含三个表的架构，这些表通过外键相互关联。它们是使用 python 创建的，如下所述(未显示 c 和 conn 的定义，因为我很确定错误并不在那里):

import sqlalchemy
import pandas as pd

# create the runsMaster table
c.execute("""CREATE TABLE IF NOT EXISTS `ngsRunStats_FK`.`runsMaster` (
    `run_ID` INT NOT NULL AUTO_INCREMENT,
    `run_name` VARCHAR(50) NULL,
    PRIMARY KEY (`run_ID`))
ENGINE = InnoDB""")

# Create the samplesMaster table
c.execute("""CREATE TABLE IF NOT EXISTS `ngsRunStats_FK`.`samplesMaster` (
  `sample_ID` INT NOT NULL AUTO_INCREMENT,
  `run_ID` INT NULL,
  `sample_name` VARCHAR(50) NULL,
  PRIMARY KEY (`sample_ID`),
  INDEX `fk_table1_runsMaster1_idx` (`run_ID` ASC),
  CONSTRAINT `fk_table1_runsMaster1`
    FOREIGN KEY (`run_ID`)
    REFERENCES `ngsRunStats_FK`.`runsMaster` (`run_ID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB""")

# Create the XYStats table
c.execute("""CREATE TABLE IF NOT EXISTS `ngsRunStats_FK`.`XYstats` (
  `XYstats_ID` INT NOT NULL AUTO_INCREMENT,
  `run_ID` INT NULL,
  `sample_ID` INT NULL,
  `X_TOTAL_COVERAGE` FLOAT NULL,
  `X_TARGET_COUNT` FLOAT NULL,
  `X_MEAN_TARGET_COVERAGE` FLOAT NULL,
  `Y_TOTAL_COVERAGE` FLOAT NULL,
  `Y_TARGET_COUNT` FLOAT NULL,
  `Y_MEAN_TARGET_COVERAGE` FLOAT NULL,
  `Ymeancov_Xmeancov` FLOAT NULL,
  PRIMARY KEY (`XYstats_ID`),
  INDEX `fk_XYstats_runsMaster_idx` (`run_ID` ASC),
  INDEX `fk_XYstats_samplesMaster1_idx` (`sample_ID` ASC),
  CONSTRAINT `fk_XYstats_runsMaster`
    FOREIGN KEY (`run_ID`)
    REFERENCES `ngsRunStats_FK`.`runsMaster` (`run_ID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_XYstats_samplesMaster1`
    FOREIGN KEY (`sample_ID`)
    REFERENCES `ngsRunStats_FK`.`samplesMaster` (`sample_ID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB""")

samplesMaster 和 runsMaster 表都工作正常。它们是从其他迭代中自动填充的，这些迭代对于理解这个问题并不那么重要。

经过几次操作后，我想从 pandas df (XY_df) 中提取一些值并将其插入 XYStats 表中。我的 pandas df 如下所示

                   0              1     2           3
0                  X  121424.000000  64.0  1897.26000
1                  Y      14.019900   4.0     3.50497
2  Ymeancov/Xmeancov       0.001847   NaN         NaN

下面是可以使用XY_df.to_dict()从表中获取的字典

{0: {0: 'X', 1: 'Y', 2: 'Ymeancov/Xmeancov'},
 1: {0: 121424.0, 1: 14.0199, 2: 0.00184739},
 2: {0: 64.0, 1: 4.0, 2: nan},
 3: {0: 1897.26, 1: 3.5049699999999997, 2: nan}}

我用来填充 XYStats 表的代码如下所示:

c.execute(f"""INSERT INTO XYstats (run_ID, sample_ID, X_TOTAL_COVERAGE, X_TARGET_COUNT, X_MEAN_TARGET_COVERAGE, Y_TOTAL_COVERAGE, Y_TARGET_COUNT, Y_MEAN_TARGET_COVERAGE, Ymeancov_Xmeancov)
VALUES
('{runID}', 
'{sampleID}',
'{XY_df.iloc[0,1]}',
'{XY_df.iloc[0,2]}',
'{XY_df.iloc[0,3]}',
'{XY_df.iloc[1,1]}',
'{XY_df.iloc[1,2]}',
'{XY_df.iloc[1,3]}',
'{XY_df.iloc[2,1]}'
""")
conn.commit()

但后来我明白了

ProgrammingError: 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 '' at line 11

我认为这根本没有提供任何信息

我很确定我的错误不在于

1. 表创建。我一直按原样使用 runsMaster 和 samplesMaster
2. 我尝试插入到 XYStats 表中的数据类型> XY_df 是 pandas 数据框，我尝试插入的内容(例如 XY_df .iloc[0,3]) 为 numpy.float64 (type(XY_df.iloc[0,1]))

但除此之外，我对发生的情况一无所知，因为我收到的错误消息非常模糊。

Answer 1

该错误是您在 SQL 上执行的查询中的语法错误。 VALUES 后面有一个未闭合的括号。您需要做的就是在查询字符串末尾添加一个右括号，然后就可以开始了:

c.execute(f"""INSERT INTO XYstats (run_ID, sample_ID, X_TOTAL_COVERAGE, X_TARGET_COUNT, X_MEAN_TARGET_COVERAGE, Y_TOTAL_COVERAGE, Y_TARGET_COUNT, Y_MEAN_TARGET_COVERAGE, Ymeancov_Xmeancov)
VALUES
('{runID}', 
'{sampleID}',
'{XY_df.iloc[0,1]}',
'{XY_df.iloc[0,2]}',
'{XY_df.iloc[0,3]}',
'{XY_df.iloc[1,1]}',
'{XY_df.iloc[1,2]}',
'{XY_df.iloc[1,3]}',
'{XY_df.iloc[2,1]}')
""")