Two problems with mysql

[void285]

I have been used pymedoo for several sqlite projects and it works well. Today I try to use it for mysql and found two problems. The script works well if change dbtype to sqlite from mysql.

1. table name quoted error

pymysql.err.ProgrammingError: (1064, u'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 '"test" LIMIT 1' at line 1'):
SELECT * FROM "test" LIMIT 1

I modify quote function in dialect.py to solve the problem, but not sure if this will cause more error.

    @staticmethod
    def quote(item):
        """How to quote values"""
        return str(item)

2. recordset not iteratable

File "C:\Python27\lib\site-packages\medoo\record.py", line 190, in next
nextrow = Record(self.meta, list(next(self._cursor)), readonly = self.readonly)
TypeError: Cursor object is not an iterator

This error occurs both in 0.0.3 with python27 or 0.0.5 with python37.

[pwwang]

Hi,

Sorry for the late response.

1. Can you post your code on how you generated that SQL using pymedoo?
2. The cursor object is originated from connect function of pymysql package.
   See

   pymedoo/medoo/base.py

   Line 26 in aefbd16

   self.cursor = self.connection.cursor()

   and here

   pymedoo/medoo/database/mysql.py

   Line 22 in aefbd16

   return pymysql.connect(**arguments)

Can you post the your pymysql version, as well as the SQL or the pymedoo code you used to generate the SQL?

Thank you.

[void285]

Hi, my packages version and code is here:
python: Python 3.7.4 (tags/v3.7.4:e09359112e, Jul 8 2019, 20:34:20) [MSC v.1916 64 bit (AMD64)] on win32

pymysql: PyMySQL-0.9.3.dist-info

medoo: pymedoo-0.0.5.dist-info

code:

# coding: utf-8

from medoo import Medoo
db = Medoo(dbtype='mysql', database='books', host='localhost', user='root', password='root', charset='utf8mb4')
rs = db.select('test', '*', {'LIMIT': 3})
for r in rs:
    print(r.id)

error if use the original dialect.py:

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 \'"test" LIMIT 3\' at line 1')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "parse3.py", line 5, in <module>
    rs = db.select('test', '*', {'LIMIT': 3})
  File "C:\Python37\lib\site-packages\medoo\base.py", line 99, in select
    return self.query(sql, commit, readonly)
  File "C:\Python37\lib\site-packages\medoo\base.py", line 132, in query
    raise type(ex)(str(ex) + ':\n' + self.sql)
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 \'"test" LIMIT 3\' at line 1'):
SELECT * FROM "test" LIMIT 3

error if use the modified dialect.py

Traceback (most recent call last):
  File "parse3.py", line 6, in <module>
    for r in rs:
  File "C:\Python37\lib\site-packages\medoo\record.py", line 180, in __iter__
    yield next(self)
  File "C:\Python37\lib\site-packages\medoo\record.py", line 190, in __next__
    nextrow = Record(self.meta, list(next(self._cursor)), readonly = self.readonly)
TypeError: 'Cursor' object is not an iterator

Hope this will help, Thank you!

[pwwang]

I can reproduce this using mysql5.7, and others as the same as you listed.

I found the problem is the mode of MySQL being used.
See https://stackoverflow.com/questions/13884854/mysql-double-quoted-table-names and https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi_quotes

I am going to change the quote style for MySQL to ` for compatibility reason.

[pwwang]

For the second issue, I found it is because pymysql was updated to have the schema of their cursor class changed. At the beginning when I was developing this package, it supported "standard" cursor like packages for other databases do (including iterating and description for the cursor). Fortunately, mysql has developed its own python client, which supports the standard cursor.

So I decided to drop pymysql as backend for MySQL database and use mysql.connector instead.
(to install that: pip install mysql-connector-python)

[pwwang]

Auto closed by commit. Feel free to reopen it if you still have problems.

[void285]

Hi,

I found sqlite engine stop working after the latest update. This code snippet will not create the db file. If I run it with earlier version to create the db file, and run it again with 0.0.6, print(tables) will print [].

from medoo import Medoo

db = Medoo(dbtype="sqlite", database="z:\\test.db")
tables = db.query("SELECT name FROM sqlite_master WHERE type='table';")
tables = [x['name'] for x in tables.all()]
print(tables)
if 'books' not in tables:
    db.query('''create table books
        (id integer primary key,
        title varchar(25) not null default '',
        author varchar(6) not null default ''
        );''')

[void285]

I submit this as new issue and failed, so comment here.

[pwwang]

Close via #7

[shkumbinhasani]

Hello,

I am facing the same error, its still using pymysql maybe the vesion in PIP is still the old one?

[pwwang]

@shkumbinhasani
What is the output of:

import medoo
print(medoo.__version__)

?

[shkumbinhasani]

Sure

Traceback (most recent call last):
  File "C:/Users/sh442/pythonProjects/medooTest/main.py", line 2, in <module>
    print(medoo.__version__)
AttributeError: module 'medoo' has no attribute '__version__'

But while installing

Collecting medoo
  Using cached medoo-0.0.3-py2.py3-none-any.whl (15 kB)
Collecting six
  Using cached six-1.15.0-py2.py3-none-any.whl (10 kB)
Installing collected packages: six, medoo
Successfully installed medoo-0.0.3 six-1.15.0