I wrote the following code to SELECT about 1500 data in a certain table, but for some reason I could only get 1000 data.
sample_before.py
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
schema = 'mysql://user_id:password@localhost/sample'
engine = create_engine(schema, echo=False, encoding='utf-8')
Base = declarative_base(engine)
class Model(Base):
__tablename__ = 'sample'
__table_args__ = {'autoload': True}
def __repr__(self):
return "<sample class>"
Session = sessionmaker(bind=engine)()
records = Session.query(Model)
print "RECORDS: %d" % records.count()
for record in records:
print 'ID: %d, NAME:%s, ADDRESS:%s, BIRTHDAY:%s'
When I run the above code, `records.count ()` becomes 1000.
I haven't manipulated more than 1000 data before, so I investigated it.
First of all, the environment I am using is as follows.
As a result of investigation, it was found that the default cursor of MySQLdb is on the local program side, which imposes a limit.
Since it seems that I should have the cursor on the server side, I modified the code as follows.
sample_after.py
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import MySQLdb.cursors
schema = 'mysql://user_id:password@localhost/sample'
engine = create_engine(schema, echo=False, encoding='utf-8',
connect_args={'cursorclass': MySQLdb.cursors.SSCursor})
Base = declarative_base(engine)
class Model(Base):
__tablename__ = 'sample'
__table_args__ = {'autoload': True}
def __repr__(self):
return "<sample class>"
conn = engine.connect()
Session = sessionmaker(bind=conn)()
records = Session.query(Model)
print "RECORDS: %d" % records.count()
for record in records:
print 'ID: %d, NAME:%s, ADDRESS:%s, BIRTHDAY:%s'
I don't know what happens if I use another driver of MySQL (MariaDB)? I don't know because I haven't investigated it, but I think it will work if I set it with connect_args.
If anyone knows another good way, please let me know.
Recommended Posts