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 a...@happy.cat 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
