3

I am a newbie and I am trying to create a django project on Windows 8 with Python 3 and Django 1.8.6 using mysql database

When I execute python manage.py migrate I get an error as 

django.db.utils.ProgrammingError: (1064 ....

I have cross checked my settings.py file and everything. In fact the connection is established and sometimes it successfully creates tablesdjango_content_type and django_migrations and then gives this error and sometimes without creating these tables itself I get this error.

The stack trace printed ends as follows :

django.db.utils.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 '%s' at line 1")

I have also referred and tried to follow the instructions (installed mysql-connector-python-2.1.3) in other similar queries at this site, but nothing seems to be working

The complete stack trace is as follows :

D:\pythonLearn\learningDjango\mysite>python manage.py migrate
Traceback (most recent call last):
File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params)
File "C:\Python34\lib\site-packages\django\db\backends\mysql\base.py", line 124, in execute return self.cursor.execute(query, args)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-md64.egg\MySQLdb\cursors.py", line 207, in execute self.errorhandler(self, exc, value)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-md64.egg\MySQLdb\connections.py", line 37, in defaulterrorhandler raise errorvalue
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-md64.egg\MySQLdb\cursors.py", line 192, in execute r = self._query(query)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-md64.egg\MySQLdb\cursors.py", line 356, in _query rowcount = self._do_query(q)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-md64.egg\MySQLdb\cursors.py", line 320, in _do_query db.query(q)
_mysql_exceptions.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 '%s' at line 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 354, in execute_from_command_line utility.execute()
File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 346, in execute self.fetch_command(subcommand).run_from_argv(self.argv)
File "C:\Python34\lib\site-packages\django\core\management\base.py", line 394, in run_from_argv self.execute(*args, **cmd_options)
File "C:\Python34\lib\site-packages\django\core\management\base.py", line 445, in execute output = self.handle(*args, **options)
File "C:\Python34\lib\site-packages\django\core\management\commands\migrate.py", line 93, in handle executor = MigrationExecutor(connection, self.migration_progress_callback)
File "C:\Python34\lib\site-packages\django\db\migrations\executor.py", line 19, in __init__self.loader = MigrationLoader(self.connection)
File "C:\Python34\lib\site-packages\django\db\migrations\loader.py", line 47, in __init__self.build_graph()
File "C:\Python34\lib\site-packages\django\db\migrations\loader.py", line 188, in build_graph   self.applied_migrations = recorder.applied_migrations()
File "C:\Python34\lib\site-packages\django\db\migrations\recorder.py", line 59, in applied_migrations self.ensure_schema()
File "C:\Python34\lib\site-packages\django\db\migrations\recorder.py", line 53, in ensure_schema editor.create_model(self.Migration)
File "C:\Python34\lib\site-packages\django\db\backends\base\schema.py", line 289, in create_model self.deferred_sql.extend(self._model_indexes_sql(model))
File "C:\Python34\lib\site-packages\django\db\backends\mysql\schema.py", line 56, in _model_indexes_sql self.connection.cursor(), model._meta.db_table
File "C:\Python34\lib\site-packages\django\db\backends\mysql\introspection.py", line 142, in get_storage_engine
"WHERE table_name = %s", [table_name])
File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 79, in execute return super(CursorDebugWrapper, self).execute(sql, params)
File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params)
File "C:\Python34\lib\site-packages\django\db\utils.py", line 98, in __exit__ six.reraise(dj_exc_type, dj_exc_value, traceback)
File "C:\Python34\lib\site-packages\django\utils\six.py", line 658, in reraise raise value.with_traceback(tb)
File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 64, in execute return self.cursor.execute(sql, params)
File "C:\Python34\lib\site-packages\django\db\backends\mysql\base.py", line 124, in execute return self.cursor.execute(query, args)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-amd64.egg\MySQLdb\cursors.py", line 207, in execute self.errorhandler(self, exc, value)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-amd64.egg\MySQLdb\connections.py", line 37, in defaulterrorhandler raise errorvalue
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-amd64.egg\MySQLdb\cursors.py", line 192, in execute r = self._query(query)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-amd64.egg\MySQLdb\cursors.py", line 356, in _query rowcount = self._do_query(q)
File "C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-amd64.egg\MySQLdb\cursors.py", line 320, in _do_query db.query(q)
django.db.utils.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 '%s' at line 1")
Improve this question
10
  • 1
    so, please put up the code you've executed. Commented Nov 21, 2015 at 13:41
  • @hsfzxjy So the only thing that I have written is in settings.py which is as follows : DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME' : 'polldb', 'USER' : 'root', 'PASSWORD' : 'passwd', 'HOST' : 'localhost', } } because I am still trying to create tables through migrate Commented Nov 21, 2015 at 13:45
  • If you are using the django.db.backends.mysql backend with Python 3, then you should be using mysqlclient, not mysql-connector-python. The docs warn that mysql-connector-python may not work with the latest version of Django, and I have seen other questions from users on Stack Overflow who were having problems with it. Commented Nov 21, 2015 at 14:38
  • @Alasdair so presently I have both installed. Do you suggest that I uninstall mysql-connector-python Commented Nov 21, 2015 at 14:42
  • @Alasdair mysqlclient was installed using pip install mysqlclient - this says that it is installed in c:\users\vandana\appdata\roaming\python\python34\site-packages . Do I need to set some environment variable as well Commented Nov 21, 2015 at 14:46

3 Answers 3

Reset to default
1

django.db.utils.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 '(6) NOT NULL)' at line 1")

Need to upgrade mysql to 5.6 or later. This is caused because of DATETIME(6). which is supported 5.6 or lated version.

Reference https://docs.djangoproject.com/en/2.1/ref/databases/#fractional-seconds-support-for-time-and-datetime-fields

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Your error message says ...

...for the right syntax to use near '%s' at line 1

This means the query your program passed to MySQL contained the text string %s. It looks to me like your program has not substituted a value into that string. It must do so before trying to use the string as a query.

This business of using placeholders in queries is commonplace. Is it possible your query says something like this?

 INSERT INTO names (name) VALUES (%s)

It needs to say this instead

 INSERT INTO names (name) VALUES ('Ban Ki Moon')
Improve this answer

1 Comment

this is an error reported on file C:\Python34\lib\site-packages\mysql_python-1.2.4-py3.4-win-amd64.egg\MySQLdb\cursors.py", line 320, in _do_query which is python library file. Moreover I have not written any code at all - I am still at the migrate stage only
0

As @krishna chandak said, you should upgrade the MySQL version because of DATETIME(6).

How to update MySQL version:

Make sure yum is up-to-date

yum update yum

Install yum-utils (it provides yum-config-manager)

yum install yum-utils

Backup: dump all databases to all_dbs.sql

mysqldump --lock-all-tables --all-databases -uroot -yourpassword >/root/all_dbs.sql

Stop MySQL daemon

service mysqld stop

Download latest MySQL

------ for CentOS 6

wget https://dev.mysql.com/get/mysql80-community-release-el6-2.noarch.rpm
sudo rpm -Uvh mysql80-community-release-el6-2.noarch.rpm

------ for CentOS 7

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm

Remove old version and install 5.7 version

sudo yum-config-manager --enable mysql57-community
sudo yum-config-manager --disable mysql80-community
yum remove mysql55-server
yum install mysql57-server
yum update mysql

Start MySQL

service mysqld start

If you get any errors you have to remove deprecated variables in etc/my.cnf

Upgrade existing databases

mysql_upgrade -uroot -p

IF YOU HAME ERROR WITH *.sock

Change config /etc/my.cnf by adding

datadir=/var/lib/mysql

and commenting this string

# socket=/var/lib/mysql/mysql.sock

Then do steps to updating database and return config to previous state.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.