I have an ADT that I can modify using Advantage Data Architect.
However I wish to be able to modify the table using adsdb. I've created the table using;
cnxn = adsdb.connect(DataSource='c:/Python27/', ServerType='1')
cursor = cnxn.cursor()
cursor.execute('CREATE TABLE Persons (PersonID INTEGER, LastName CHAR(100), FirstName CHAR(100))'
cursor.execute('INSERT INTO Persons (PersonID) VALUES (01)')
cursor.execute('INSERT INTO Persons (LastName) VALUES ("Smith")')
adsdb.OperationalError: Error 7200: AQE Error: State = S0000; NativeError = 2121; [iAnywhere Solutions][Advantage SQL Engine]Column not found: Smith -- Location of error in the SQL statement is: 40
In ADS SQL (and indeed in ANSI-SQL) strings (CHAR typed) values have to be enclosed in single quotes:
INSERT INTO Persons (LastName) VALUES ('Smith')
In Python a string literal can either be written in single or double quotes:
Since the correct SQL statement does not contain double quotes it would be easier to use a double quoted string literal:
cursor.execute("INSERT INTO Persons (LastName) VALUES ('Smith')")
If you want to use a single quoted string literal, you have to escape the single quotes inside the literal:
cursor.execute('INSERT INTO Persons (LastName) VALUES (\'Smith\')')
But I wouldn't do it like that, since using string interpolation or string concatenation to get values into an SQL statement is very dangerous and can lead to SQL injection.
The correct way would be using a parameter:
cursor.execute('INSERT INTO Persons (LastName) VALUES (?)', 'Smith')
BTW: "Persons" is a terrible table name (The plural of person is people, you should either use "person" oder "people" as the table name).