尝试使用Python将数据插入MySQL表时发生编程错误

Question

虽然我对SQL非常陌生，但我已经使用python构建了DBs，但现在我被卡住了

简单地说，我有一个带有三个表的模式，它们通过外键相互关联。它们是使用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表都工作正常。它们是从其他迭代中自动填充的，这些迭代对于理解这个问题并不重要

经过一些操作后，我想从一个表（XY_df）中提取一些值并插入到XYStats表中。我的熊猫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上执行的查询中的语法错误。值后面有一个未闭合的括号。您只需在查询字符串的末尾添加一个右括号，就可以开始了：

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]}')
""")