sqlAlchemy never insert data to the database,!

[xinghanchuanqizibenguanliyou]

I use the example you give.

[xinghanchuanqizibenguanliyou]

I found the problem, it doesn't support InnoDB store engine,,, but worckbench default set to it.

[jettify]

That is strange, travisci tests aiomysql with different versions of MySQL and MariaDB, they use InnoDB backend by default, as far as I can tell.

[xinghanchuanqizibenguanliyou]

i didn't try pool or single connect yet, I just try aiomysql type of sqlalchemy.
you can see my github address https://github.com/adajass/privatesolution ,
just change the engine in database.sql file to see this problem. I'm not sure it will appear, but maybe.
you must sure the database connect's parameters are correct!

[jettify]

Could you check your MySQL version and configured engine?

[popravich]

Hi, @xinghanchuanqizibenguanliyou
First of all, your code in create_table drops your table, so this might be your problem.

[xinghanchuanqizibenguanliyou]

@popravich I have 3 tables, and i can't see the data be inserted by mysql client. What's more, i changed the engine to be MyISAM, all goes well.

[popravich]

Are there any errors or exceptions?
Try several other ways of inserting data (into table with InnoDB engine):

• raw yield from conn.execute("INSERT INTO tbl (id, val) VALUES (1, \"val\");");
• sa yield form conn.execute(tbl.insert().values(id=1, val='val'))

[rudyryk]

Just my guess - aiomysql uses autocommit=False by default, that means any query is not effectively saved to database until committed.

I'd vote for settings autocommit=True by default, just like aiopg does.

[xinghanchuanqizibenguanliyou]

@rudyryk yeah, must run " yield from conn.execute('commit') " after every insertion.

[xinghanchuanqizibenguanliyou]

@rudyryk but you can't explain it goes well with MyISAM db engine.

[rudyryk]

@xinghanchuanqizibenguanliyou Not sure, but I suppose engines just deal a little bit differently with commit policy :)

[jettify]

@rudyryk

I'd vote for settings autocommit=True by default, just like aiopg does.

aiomysql follows PyMySQL, where autocommit is false by default, as for aiopg as far as I remember autocommit=True by default is limitation of psycopg async interface (possibly I missed something)

[xinghanchuanqizibenguanliyou]

@jettify I don't recommend you change it, for the sake of efficient. it will goes well when write such code:
for item in timegap[1:]: yield from conn.execute(his.insert().values(dtime=item, objectid= objid)) yield from conn.execute('commit')

[rudyryk]

@xinghanchuanqizibenguanliyou Not sure about efficiency, the Tip 5 from Instagram team claims that autocommit mode is significantly more efficient for Psycopg2: http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from

I think that backwards compatibility is far more important and such change would require wider discussion. That's why I said that I would vote :)

And I've occasionally found the answer to your question about the difference between InooDb/MyISAM: "autocommit is on by default for InnoDB - MyISAM doesn't support transactions." http://stackoverflow.com/questions/2950676/difference-between-set-autocommit-1-and-start-transaction-in-mysql-have-i-misse

@jettify Oh, I see. Well, OK - probably that's important for people who migrate their code from sync to async. I don't really know if there are many of them :) For starting a new project from scratch with the new approach, autocommit model just seems more clear to me -- if you don't explicitly start a transaction, queries are performed immediately, and that's the default behavior for MySQL, after all :)

[rudyryk]

@jettify Oh, and the initial issue is not with raw queries, it's about sqlAlchemy. As far as I know, if no transaction is defined, queries are committed automatically. The example in "Example of SQLAlchemy optional integration" doesn't contain commit() - and that's how it should work with sqlAlchemy, am I right?

[jettify]

good question!
May be we need to start thinking about adding commit method to SAConnection and making autocommit=True by default for SQLAlchemy ?

[AdaJass]

for item in range(100):
`` yield from conn.execute(his.insert().values(id=item))
yield from conn.execute('commit')

well i think this way well more efficient than below:

for i in range(100:
``yield from conn.execute(his.insert().values(id=i))`` yield from conn.execute('commit')

that is what @xinghanchuanqizibenguanliyou mean.

[rudyryk]

@jettify As I understand sqlAlchemy original engine implements auto-commit logic by default for queries executed outside of a session, as they call it. And this is independent on underlying database, as long it's ORM and all code has to be database agnostic as much as possible.

I'm not sure how to deal with this case. I see two options:

1. Set autocommit=True for sqlAlchemy connections
2. Add commit statement to SAConnection._execute()

The first options seem correct to me if connections created for sqlAlchemy are only used by sqlAlchemy (not used separately for raw queries). Otherwise I'd look at second option.