2

I am using Python3 and PyMySQL 0.7.9. Version of Mysql is 5.5.57.

Using this query to fetch data from Mysql works:

cur.execute('SELECT date FROM Tablename1 ORDER BY id DESC LIMIT 1')

I would like to get name of table from variable. Reading Pymysql docs and this Stackoverflow post lead me to belive that this code should work:

dbtable = Tablename1
query = 'SELECT date FROM %s ORDER BY id DESC LIMIT 1'
cur.execute(query, (dbtable, ))

But this results in error:

pymysql.err.ProgrammingError: (1064, "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 ''Tablename1' ORDER BY id DESC LIMIT 1' at line 1")

I also tried using quotes but got different error:

query = 'SELECT `date` FROM `%s` ORDER BY `id` DESC LIMIT 1'
cur.execute(query, (dbtable, ))

Results in:

pymysql.err.ProgrammingError: (1146, "Table 'Databasename.'Tablename1'' doesn't exist")

How should the query be changed to allow variable work?

Madoc Comadrin
  • 498
  • 1
  • 12
  • 27
  • 4
    your code always passing `'` character to sql statement. So, your select will become `select * from 'tablename1'`. And then, you will have the syntax error. Try to pass the table name directly in sql, like: `query = ''SELECT date FROM {} ORDER BY id DESC LIMIT 1".format(Tablename1)` – Abe Sep 06 '17 at 18:08
  • With that formatting I was able to make the query work. – Madoc Comadrin Sep 06 '17 at 19:53
  • 2
    @Abe This solution works perfectly, but doesn't it open possibility for sql injection? – spencer741 Sep 22 '19 at 16:46
  • @spencer741 unfortunately yes, I recommend to use and query builder like [SQLAlchemy](https://www.sqlalchemy.org/) and this kind of flaw could be avoided. – Abe Sep 23 '19 at 19:58

2 Answers2

-1

This works for me. Using python 3.8, mariadb 10.2, pymysql

cur = con.cursor()
cur.execute('SELECT field FROM %s ORDER BY id DESC LIMIT 1' %(dbtable))
result = cur.fetchone()

Replace field and dbtable approriately

wwmwabini
  • 75
  • 6
-2

Like this :

dbtable = input("Table name: ")
query = "SELECT * FROM %s"%(dbtable)
cur.execute(query)

results = cur.fetchall()

for row in results:
print (row[0], row[1], row[2], row[3])
An0n
  • 705
  • 6
  • 19