Hello,
I have discovered that this is a limitation of pysqlite. From a comment in
one of the test cases:
"pysqlite does not know the rowcount of SELECT statements, because we
don't fetch all rows after executing the select statement. The rowcount
has thus to be -1."
http://code.google.com/p/pysqlite/source/browse/lib/test/dbapi.py#300
It would be nice if SqlAlchemy could somehow give the same rowcount
regardless of the database engine. For example:
from sqlalchemy import create_engine, __version__
from sqlalchemy.orm import create_session
print "Testing mysql & sqlite rowcount with SqlAlchemy v%s" % __version__
mysql_engine = create_engine('mysql://user:p...@localhost/test')
sqlite_engine = create_engine('sqlite:///:memory:')
mysql_session = create_session(mysql_engine)
sqlite_session = create_session(sqlite_engine)
sql_drop = "DROP TABLE IF EXISTS users;"
sql_create = "CREATE TABLE IF NOT EXISTS users (id INT(8) NOT NULL, status "
\
"INT NOT NULL, name VARCHAR(100) NULL, PRIMARY KEY (id));"
sql_ins_1 = "INSERT INTO users (id, status, name) VALUES(1, 2, 'Dan')"
sql_ins_2 = "INSERT INTO users (id, status, name) VALUES(2, 1, 'Dave')"
sql_ins_3 = "INSERT INTO users (id, status, name) VALUES(3, 5, 'Donald')"
print "Creating schemas"
mysql_session.execute(sql_drop)
mysql_session.execute(sql_create)
sqlite_session.execute(sql_drop)
sqlite_session.execute(sql_create)
print "Inserting some data"
mysql_session.execute(sql_ins_1)
mysql_session.execute(sql_ins_2)
mysql_session.execute(sql_ins_3)
sqlite_session.execute(sql_ins_1)
sqlite_session.execute(sql_ins_2)
sqlite_session.execute(sql_ins_3)
all_mysql_rows = mysql_session.execute("SELECT * FROM users")
all_sqlite_rows = sqlite_session.execute("SELECT * FROM users")
print "all_mysql_rows.rowcount: %d" % all_mysql_rows.rowcount
print "len(all_mysql_rows.fetchall()): %d" % len(all_mysql_rows.fetchall())
print "all_sqlite_rows.rowcount: %d" % all_sqlite_rows.rowcount
print "len(all_sqlite_rows.fetchall()): %d" %
len(all_sqlite_rows.fetchall())
mysql_session.close()
sqlite_session.close()
Output is:
Testing mysql & sqlite rowcount with SqlAlchemy v0.5.8
Creating schemas
Inserting some data
all_mysql_rows.rowcount: *3*
len(all_mysql_rows.fetchall()): *3*
all_sqlite_rows.rowcount: *-1*
len(all_sqlite_rows.fetchall()): *3*
~Adam
P.s., yes, I noticed that in my last post, I was erroneously inserting
strings into an integer field!
On 12 February 2010 12:26, Adam Hayward <[email protected]> wrote:
> Hello there.
>
> (first post to group)
>
> I've been having a problem with an incorrect rowcount for ResultProxies
> using Sqlite databases. Regardless of how many rows in the resultset, it
> gives me a rowcount of "-1". Best demonstrated with an example:
>
> from sqlalchemy import create_engine, __version__
> from sqlalchemy.orm import create_session
> print "Testing sqlite rowcount with SqlAlchemy v%s" % __version__
> engine = create_engine('sqlite:///:memory:')
> session = create_session(engine)
>
> session.execute("""CREATE TABLE IF NOT EXISTS users (
> id INT(8) NOT NULL,
> status INT NOT NULL,
> name VARCHAR(100) NULL,
> PRIMARY KEY (id)
> );""")
>
> session.execute("INSERT INTO users (id, status, name) VALUES(1, 'Dan', 2)")
> session.execute("INSERT INTO users (id, status, name) VALUES(2, 'Dave',
> 1)")
> session.execute("INSERT INTO users (id, status, name) VALUES(3, 'Donald',
> 5)")
>
> result = session.execute("SELECT * FROM users")
> print result.rowcount
> allrows = result.fetchall()
> print len(allrows)
> session.close()
>
> Output is:
>
> Testing sqlite rowcount with SqlAlchemy v0.5.8
> *-1*
> 3
>
> Is this a bug? Am I doing something wrong?
>
> Regards,
>
> Adam
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
