Thanks Mike.
I've split it like this and it worked :
nengine = create_engine('mysql+mysqldb://root:1122@localhost/DB' , echo =
True)
auto_incr = text("""ALTER TABLE `table1` ... ;
ALTER TABLE ... ; """ )
nengine.connect().execute(auto_incr)
nengine2 = create_engine('mysql+mysqldb://root:1122@localhost/DB' , echo =
True)
auto_incr2 = text("""ALTER TABLE `table2`... ;
ALTER TABLE ... ;""" )
nengine2.connect().execute(auto_incr2)
So, it works for multi-line query but partially. I have to do the same for
many tables, so I thought python has something more handy to offer :)
On Thursday, 9 February 2017 20:37:00 UTC+1, Mike Bayer wrote:
>
> 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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > 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.
