ValueError: 无法将 'SOH' 转换为十进制整数

Question

我在尝试把数据插入sqlite数据库时遇到了以下错误：

Traceback (most recent call last):
  File "C:\SS\Problem13\src\database.py", line 23, in <module>
    sql = """INSERT INTO students (name, student_id, email, school_id, gender, gpa, address) VALUES ('%s','%i','%s','%i','%s','%f','%s')"""%(data[0], int(data[1]),data[2],int(data[3]),data[4],float(data[5]),data[6])
ValueError: invalid literal for int() with base 10: 'SOH'

下面是我的代码：

import sqlite3
import csv
# Connect to database, if it does not exist, it will create it.
conn = sqlite3.connect("nopoly.db")
# Creating table.
conn.execute("PRAGMA foreign_keys = 1")
conn.execute("CREATE TABLE schools (id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT NOT NULL, director TEXT NOT NULL, email TEXT NOT NULL)")
conn.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, student_id INTEGER NOT NULL, email TEXT NOT NULL, school_id INTEGER NOT NULL REFERENCES schools, gender TEXT NOT NULL, gpa FLOAT NOT NULL, address TEXT NOT NULL)")
# Opening the csv files
schoolreader = csv.reader(open("files/schools.csv"))
studentreader = csv.reader(open("files/studentsp13.csv"))
# Skip the headers
schoolreader.next()
studentreader.next()
# Extract each row and print
for data in schoolreader:
    print data
    sql = """INSERT INTO schools (name, description, director, email) VALUES ('%s','%s','%s','%s')"""%(data[0],data[1],data[2],data[3])
    print "DEBUG", sql
    conn.execute(sql)
for data in studentreader:
    print data
    sql = """INSERT INTO students (name, student_id, email, school_id, gender, gpa, address) VALUES ('%s','%i','%s','%i','%s','%f','%s')"""%(data[0], int(data[1]),data[2],int(data[3]),data[4],float(data[5]),data[6])
    print "DEBUG", sql
    conn.execute(sql)
#
conn.commit()

我知道在studentsp13.csv文件中，学校是用字符串表示的。我想知道怎么把这些字符串转换成整数，这样它们就能对应到“schools”表里。

Answer 1

我在尝试将值插入sqlite数据库时遇到了以下错误。

在插入参数到查询时，不要使用字符串格式化，而是应该把它们作为参数传递给conn.execute()方法。这样做可以保护你免受SQL注入攻击，同时数据库也会把数据当作正确的类型来处理。你可以查看SQLite的Python文档，里面有详细的说明和示例。

我知道在studentsp13.csv文件中，学校是用字符串标记的。我想知道如何将其映射为整数，以便与“schools”表对应。

当你向学校表插入数据时，可以把SQLite生成的ID记录到一个字典里，然后在插入学生数据时再查找这些ID。这个过程大概是这样的：

schools = {}
for data in schoolreader:
    print data
    sql = """INSERT INTO schools (name, description, director, email) VALUES ('%s','%s','%s','%s')"""%(data[0],data[1],data[2],data[3])
    conn.execute(sql)
    schools[data[0]] = conn.lastrowid
for data in studentreader:
    school_id = schools[data[3]]
    sql = """INSERT INTO students (name, student_id, email, school_id, gender, gpa, address) VALUES ('%s','%i','%s','%i','%s','%f','%s')"""%(data[0], int(data[1]),data[2],school_id,data[4],float(data[5]),data[6])
    conn.execute(sql)