pyodbc-使用默认值将列添加到MS Access [英] pyodbc - add column to MS Access with default value

问题描述

我正在尝试使用pyodbc和Python 3.5向MS Access数据库表中添加一列.

I'm trying to add a column to an MS Access database table using pyodbc and Python 3.5.

使用表达式

self.cursor.execute("ALTER TABLE data ADD COLUMN testColumn TEXT(10)")

可以正常工作，但是当我尝试添加默认值(DEFAULT"no")时，它将引发语法错误.我尝试了多种组合，但是没有运气.

works fine, but when I try to add a default value (DEFAULT "no"), it throws a Syntax error. I've tried multiple combinations, but no luck.

非常感谢您的帮助！

欢呼

推荐答案

不幸的是，Access ODBC驱动程序根本不支持CREATE/ALTER TABLE语句中的列的DEFAULT子句.用于Access的ODBC驱动程序和OLEDB提供程序在DDL支持方面有所不同，因此不幸的是，对于同一DDL语句，我们可以得到不一致的结果，如下面的VBScript代码使用ADO所示:

Sadly, the Access ODBC driver simply does not support the DEFAULT clause for a column in CREATE/ALTER TABLE statements. The ODBC driver and OLEDB provider for Access have diverged somewhat in their DDL support, so unfortunately we can get inconsistent results for the same DDL statement as illustrated by the following VBScript code using ADO:

OLEDB正常工作...

OLEDB works fine ...

Option Explicit
Dim conn
Set conn = CreateObject("ADODB.Connection")
Dim connStr
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\mdbTest.mdb"
conn.Open connStr

On Error Resume Next
conn.Execute "DROP TABLE Cheeses"
On Error GoTo 0
conn.Execute "CREATE TABLE Cheeses (Id LONG PRIMARY KEY, CheeseName TEXT(50) DEFAULT 'Cheddar')"

conn.Execute "INSERT INTO Cheeses (Id) VALUES (1)"

Dim rst
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT CheeseName FROM Cheeses WHERE Id = 1", conn
If rst("CheeseName").Value = "Cheddar" Then
    WScript.Echo "Success"
End If

conn.Close

...但是如果我们更改连接字符串以使用ODBC ...

... but if we change the connection string to use ODBC ...

connStr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Users\Public\mdbTest.mdb"

...然后我们执行CREATE TABLE语句的尝试失败

... then our attempt to execute the CREATE TABLE statement fails with

用于ODBC驱动程序的Microsoft OLE DB访问接口:[Microsoft] [ODBC Microsoft Access驱动程序] CREATE TABLE语句中的语法错误.

Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

TL; DR-您不能在pyodbc下的CREATE/ALTER TABLE语句中使用DEFAULT子句.

TL;DR - You can't use the DEFAULT clause in a CREATE/ALTER TABLE statement under pyodbc.

这篇关于pyodbc-使用默认值将列添加到MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！