由Python支持的PostgreSQL PSQL用户输入名称与无效整数相混淆

Question

简介： 我一直致力于在Udacity中使用Vagrant运行Ubuntu虚拟机为我的Relational Database课程构建瑞士风格锦标赛的Python支持的PostgreSQL数据库。

问题： 为什么用户输入名称会导致整数错误？

流浪：

[email protected]:/vagrant/tournament$ python tournament_test.py 
1. countPlayers() returns 0 after initial deletePlayers() execution. 
Traceback (most recent call last): 
    File "tournament_test.py", line 151, in <module> 
    testCount() 
    File "tournament_test.py", line 26, in testCount 
    registerPlayer("Chandra Nalaar") 
    File "/vagrant/tournament/tournament.py", line 50, in registerPlayer 
    cursor.execute("INSERT INTO players VALUES (%s)", (name,)) 
psycopg2.DataError: invalid input syntax for integer: "Chandra Nalaar" 
LINE 1: INSERT INTO players VALUES ('Chandra Nalaar') 
            ^

SQL：

CREATE TABLE players (player_id SERIAL UNIQUE PRIMARY KEY, player_name 
VARCHAR(40)); 

的Python：

def registerPlayer(player_name): 
    db = connect() 
    cursor = db.cursor() 
    cursor.execute("INSERT INTO players VALUES (%s)", (player_name,)) 
    player_id = cursor.fetchone()[0] 
    db.commit() 
    cursor.close() 
    db.close() 

的Python（用户输入测试）：

registerPlayer("Chandra Nalaar") 

更新1： 所以我改变了执行语句语法以包含PRMoureu推荐的行规范，并且我返回了一个新的错误。

流浪：

[email protected]:/vagrant/tournament$ python tournament_test.py 
1. countPlayers() returns 0 after initial deletePlayers() execution. 
Traceback (most recent call last): 
    File "tournament_test.py", line 151, in <module> 
    testCount() 
    File "tournament_test.py", line 26, in testCount 
    registerPlayer("Chandra Nalaar") 
    File "/vagrant/tournament/tournament.py", line 50, in registerPlayer 
    cursor.execute("INSERT INTO players (player_name) VALUES (%s)", 
(player_name,)) 
psycopg2.ProgrammingError: column "player_name" of relation "players" does 
not exist 
LINE 1: INSERT INTO players (player_name) VALUES ('Chandra Nalaar') 
         ^

导致我，我没有使用我目前的比赛数据库的结论。所以我回到了Vagrant，并用\ i导入了我的文件，这给了我一个新的错误。

流浪： PSQL：tournament.sql：11：错误：关系“的玩家”已存在

所以我更新了跌落如果我的SQL文件中存在的命令了解到从另一个线程Here，并能够通过获得Register_Player错误。

感谢PRMoureu

Answer 1

在INSERT声明你正在使用，预计所有字段的值，与player_id第一（player_id为整数）。

您需要指定一个ID：

cursor.execute("INSERT INTO players VALUES (%s, %s)", (a_new_ID, player_name,)) 

或改变语法如下：

cursor.execute("INSERT INTO players (player_name) VALUES (%s)", (player_name,))