On 4/22/15 5:45 AM, John Doe wrote:
Is there a way to output all the SQL statements into a file? I don't
mean a logging file with other information, I mean a file with
**only** SQL statements, which could ideally be run as it is from
within my database client.
Python logging will get you there:
import logging
logger = logging.getLogger("sqlalchemy.engine")
logger.setLevel(logging.INFO) # use DEBUG to get result sets also
handler = logging.FileHandler("myfile.txt")
logger.addHandler(handler)
from sqlalchemy import create_engine
e = create_engine("sqlite://")
e.execute("select 1")
I tried setting echo=True, and I also tried this:
http://stackoverflow.com/questions/6350411/how-to-retrieve-executed-sql-code-from-sqlalchemy
but I can't spot anything out of the ordinary.
I profiled the code, and saw that 99% of the time is spent in the
do_executemany method of sqlalchemy\engine\default.py , which arguably
doesn't reveal much per se unless I understand better how
pandas.DataFrame.to_sql calls sqlachemy
This is the main thing you'd want to see when data is being inserted
into the database and indicates the most efficient route is being taken.
But reading that surprised me.
I carefully re-read your first email, and now I see, that you are trying
to write *TO* the database, yes? And you're comparing the speed of
writing *TO* to the speed of reading *FROM*?
Unfortunately, selecting data from some tables compared to inserting
data in those tables are two completely different activities. It is
not at all surprising to see a large difference in speed. Particularly
if the table has a lot of indexing on it, or we're putting in very large
data values, inserts can be slow. It also varies much by database
backend and the configuration and environment of those databases. SQL
Server can be a very fast database but it is not always that easy to tune.
99% of the time spent in do_execute() actually means most of the time is
spent outside of SQLAlchemy, within the database driver inserting those
rows.
The echo output will show you more though bulk INSERT statements will be
very long in the logfile because we log a segment of the parameters as well.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.