[PYTHON] How to get more than 1000 data with SQLAlchemy + MySQLdb

In a certain program

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:[email protected]/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.

Middleware related

Python related

Cause

As a result of investigation, it was found that the default cursor of MySQLdb is on the local program side, which imposes a limit.

Countermeasures

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:[email protected]/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'

Points of measures

in conclusion

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

How to get more than 1000 data with SQLAlchemy + MySQLdb
How to update with SQLAlchemy?
How to Alter with SQLAlchemy?
How to Delete with SQLAlchemy?
How to deal with imbalanced data
How to deal with imbalanced data
How to get started with Scrapy
How to get started with Python
How to get started with Django
How to Data Augmentation with PyTorch
How to INNER JOIN with SQLAlchemy
[Introduction to Python] How to get data with the listdir function
[Python] How to get a value with a key other than value with Enum
How to read problem data with paiza
How to use SQLAlchemy / Connect with aiomysql
How to get started with laravel (Linux)
[Django] How to get data by specifying SQL.
I tried to get CloudWatch data with Python
How to scrape horse racing data with BeautifulSoup
How to get article data using Qiita API
[Introduction to Python] How to get the index of data with a for statement
How to use xgboost: Multi-class classification with iris data
How to scrape image data from flickr with python
How to get a logged-in user with Django's forms.py
How to convert horizontally held data to vertically held data with pandas
How to extract non-missing value nan data with pandas
How to get mouse wheel verdict with Python curses
How to extract non-missing value nan data with pandas
Get table dynamically with sqlalchemy
Get Youtube data with python
Introduction to RDB with sqlalchemy Ⅰ
How to cast with Theano
How to separate strings with','
How to RDP with Fedora31
How to handle data frames
How to extract other than a specific index with Numpy
How to get into the python development environment with Vagrant
Try to get data while port forwarding to RDS with anaconda.
How to get an overview of your data in Pandas
Get additional data to LDAP with python (Writer and Reader)
[Python / Ruby] Understanding with code How to get data from online and write it to CSV
How to cancel RT with tweepy
How to extract features of time series data with PySpark Basics
[Python] How to FFT mp3 data
How to handle session in SQLAlchemy
Python: How to use async with
Link to get started with python
How to read e-Stat subregion data
Connect to multiple databases with SQLAlchemy
How to get the ID of Type2Tag NXP NTAG213 with nfcpy
How to use virtualenv with PowerShell
How to install python-pip with ubuntu20.04LTS
How to get the Python version
Introduction to RDB with sqlalchemy II
How to get all traffic through VPN with OpenVPN on Linux
How to deal with DistributionNotFound errors
How to use FTP with Python
How to calculate date with python
How to install mysql-connector with pip3
Try to get CloudWatch metrics with re: dash python data source
Addicted to character code by inserting and extracting data with SQLAlchemy