python sqlite3插入表错误: cursor is not connected?

Question

我尝试使用 sqlite3 将数据导入我的 AWS RDS 数据库中的表 babyName。对于我尝试的两种方法，第一种 data_entry() 每次都工作正常，但第二种 new_data_entry() 给了我

Cursor is not connected

或

Not all parameters are used

错误。你能帮帮我吗？

import mysql.connector
from mysql.connector import errorcode

# start connection
try:
    cnn = mysql.connector.connect(
    user = '*****',
    password = '*****',
    host = '*****-mysql.*****.us-****-1.rds.amazonaws.com',
    database = '*******')
    print('It works!')
except mysql.connector.Error as e:
    if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Somethign is wrong with username or password')
    elif e.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exist')
    else:
        print(e)

# start cursor
import sqlite3
c = cnn.cursor()
def creat_table():
    c.execute("CREATE TABLE IF NOT EXISTS babyName (name TEXT, gender TEXT, frequency INTEGER, year TEXT)")
def data_entry():
    c.execute("INSERT INTO babyName VALUES ('Mary', 'F', 1234, '2008')")
    cnn.commit()
    c.close()
    cnn.close()

def new_data_entry():
    name = 'Wendy'
    gender = 'F'
    frequency = 321
    year = '2006'
    c.execute("INSERT INTO babyName (name, gender, frequency, year) VALUES (?, ?, ?, ?)", (name, gender, frequency, year))
    cnn.commit()
    c.close()
    cnn.close()

# creat_table()
data_entry()
print('It works!')
new_data_entry()

我不断收到的错误信息:

It works!
It works!
Traceback (most recent call last):
  File "/Users/*****/sqlite3_python_cc.py", line 53, in <module>
    new_data_entry()
  File "/Users/*****/sqlite3_python_cc.py", line 45, in new_data_entry
    c.execute("INSERT INTO babyName (name, gender, frequency, year) VALUES (?, ?, ?, ?)", values)
  File "/Users/*****/anaconda/lib/python3.6/site-packages/mysql/connector/cursor.py", line 529, in execute
    raise errors.ProgrammingError("Cursor is not connected")
mysql.connector.errors.ProgrammingError: Cursor is not connected

Answer 1

在data_entry 结束时，您关闭了与数据库cnn 的连接，它在全局范围内保存为一个变量。当您尝试运行 new_data_entry 时，连接已经关闭，这就是给您带来错误的原因。

相反，在完成之前保持连接打开。

import sqlite3
c = cnn.cursor()

def creat_table():
    c.execute("CREATE TABLE IF NOT EXISTS babyName (name TEXT, gender TEXT, frequency INTEGER, year TEXT)")
def data_entry():
    c.execute("INSERT INTO babyName VALUES ('Mary', 'F', 1234, '2008')")
    cnn.commit()

def new_data_entry():
    name = 'Wendy'
    gender = 'F'
    frequency = 321
    year = '2006'
    c.execute("INSERT INTO babyName (name, gender, frequency, year) VALUES (?, ?, ?, ?)", (name, gender, frequency, year))
    cnn.commit()

def finish():
    c.close()
    cnn.close()

data_entry()
print('It works!')
new_data_entry()
finish()