the most likely cause is the bundling of multiple statements into a single execution with the semicolon. This is not a use that's normally supported by the drivers. Use two separate text() / execute() statements.



On 02/09/2017 02:25 PM, Marios Argyriou wrote:
Hi all,

I'm building a python (ver:2.7) app with MySQL as the backend db and
sqlalchemy as the handler. I'm new to sqlalchemy module.

I'm trying to execute raw SQL code through my python. The reason is I
want to change the `id` of a table, which is not the primary key, to be
auto_incremented; a thing that is not supported for MySQL through
sqlalchemy .
The code looks like this :

#creation of 2 tables, Applications and Api_Calls
metadata = MetaData(bind=self.engine)
self.app_table = self.Applications()
self.apicall_table = self.Api_Calls()
self.base.metadata.create_all(self.engine)
#the problematic part
auto_incr = text("""ALTER TABLE `table`.`Applications` DROP PRIMARY KEY,
ADD UNIQUE KEY(id);
                ALTER TABLE `Applications` ADD PRIMARY KEY(md5); """ )
nengine = create_engine('mysql+mysqldb://root:1122@localhost/Database' ,
echo = True)
self.engine = nengine
self.engine.execute(auto_incr)

Some clarification : i'm creating a new engine 'nengine' cause the
previous one is created before the creatin of 'Database' schema. Then
the new engine is assigned to 'self.engine'. The weird thing is that
when I run the application for the first time, I get no errors. When I
issue a DROP DATABASE and restart the application , I get the 2014
error. My python app doesn't support threads .Moreover, I want to
execute the SQL query for multiple tables , which will look smthng like :

with self.engine.connect() as con:
     for line in data:
     text("""ALTER TABLE `%s` DROP PRIMARY KEY, ADD UNIQUE KEY(%s);
     TABLE `Applications` ADD PRIMARY KEY(%s); """ %(line['table'] ,
line['id'], line['key']))
     con.execute(auto_incr)

As i've understand be searching for this issue, there is a problem in
concurrency and it's SQL fault.
How should I deal with the error ?
I've already searched the group but didn't find a suitable answer.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Reply via email to