Hi I’m trying to us python-sql with mysql and I really want it to work as SQL seems like something out of the COBAL era.
Here is some example code and the generated output
#! python3.8
import mysql.connector as mysql
import os, sys
from sql import *
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "isfdb",
#~ auth_plugin='mysql_native_password'
)
cu = db.cursor()
cu.execute('USE ISFDB;')
print('Manual SELECT Generation\n')
select = 'SELECT * FROM authors AS a LIMIT 10'
cu.execute(select)
authors = cu.fetchall()
if not authors:
print('No authors')
sys.exit()
for a in authors:
print('Authors', a[0], a[1])
print('\nAutomatic SELECT Generation\n')
user = Table('authors')
select = user.select(limit = 10)
print(select)
print(tuple(select))
cu.execute(*select)
authors = cu.fetchall()
if not authors:
print('No authors')
sys.exit()
for a in authors:
print('Authors', a[0], a[1])
################################
c:\programs\apps\pyw "e2.py"
Manual SELECT Generation
Authors 1 Dafydd ab Hugh
Authors 2 Robert Asprin
Authors 3 Poul Anderson
Authors 4 Piers Anthony
Authors 5 Isaac Asimov
Authors 127348 Valérie Delille
Authors 6 Alfred Bester
Authors 7 James Blish
Authors 8 David Brin
Authors 9 Greg Bear
Automatic SELECT Generation
SELECT * FROM "authors" AS "a" LIMIT 10
('SELECT * FROM "authors" AS "a" LIMIT 10', ())
Traceback (most recent call last):
File "C:\Programs\python38\lib\site-packages\mysql\connector\connection_cext.py", line 516, in cmd_query
self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"authors" AS "a" LIMIT 10' at line 1
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "e2.py", line 30, in <module>
cu.execute(*select)
File "C:\Programs\python38\lib\site-packages\mysql\connector\cursor_cext.py", line 269, in execute
result = self._cnx.cmd_query(stmt, raw=self._raw,
File "C:\Programs\python38\lib\site-packages\mysql\connector\connection_cext.py", line 521, in cmd_query
raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"authors" AS "a" LIMIT 10' at line 1
>Exit code: 1
#######################################
The only difference between the two select commans is the double quotes around names.
I dont understand what I’m doin, So any help gratefully recieved.
I’m stuck with mysql as that is what the isfdb uses.
John