Using Python to plot graphs from the mySQL database
<p>I am new to python and mySQL. Initially, I want to generate few graphs using the data which I have in mySQL database. At the end I want to generate a pdf report containing that graph. Any help/advice it will be great if you guys can point me in the right direction. Thanks very much. </p>

<p>I am generating graphs from a database with >10 million entries. The aggregating is mostly done by the database itself. Some operations take a lot of time, but it's ok. </p>

<p>The connection to the database is easy with sqlalchemy and mysqlconnector. There are many others connectors for mysql. </p>

<p>Here a little test script for sqlalchemy. The tables are generated in this example.</p>

<p>You can find more at <a href="http://www.sqlalchemy.org/" rel="nofollow">http://www.sqlalchemy.org/</a></p>

<pre><code>from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, BigInteger, Text, Index
from sqlalchemy.orm.scoping import scoped_session


Base = declarative_base() 
class ATestEntity(Base):
    __tablename__ = 'a_test_entity_table'

    id = Column(String(32), primary_key=True)
    astring = Column(String(32))

    aint = Column(BigInteger, default=-1)


dialect = "mysql+mysqlconnector"
username = "username"
password = "passwort"
host = "localhost"
port = "3306"
database = "tests"

dbconnector = '%s://%s:%s@%s:%s/%s?charset=utf8mb4&amp;use_unicode=0' % \
    (dialect, username, password, host, port, database, )
engine = create_engine(dbconnector)
session_factory = sessionmaker(autocommit=False, autoflush=False)
session_factory.configure(bind=engine)

Base.metadata.create_all(engine)

s = scoped_session(session_factory)
try:
    #Create a new entity
    a_obj = ATestEntity()
    a_obj.id = 'test'
    a_obj.astring = 'this is a test'
    a_obj.aint = 10
    s.add(a_obj)
    s.commit()

    #Delete a entity
    s.query(ATestEntity).filter(ATestEntity.id == 'test').delete()
    s.commit()

except:
    s.rollback()
    raise
</code></pre>

<p>The mathematical calculation outside can be done with numpy and scipy if needed.
<a href="http://www.numpy.org/" rel="nofollow">http://www.numpy.org/</a>
<a href="https://www.scipy.org/" rel="nofollow">https://www.scipy.org/</a></p>

<p>The plot can be done with matplotlib. There are many examples on.
<a href="http://matplotlib.org/examples/index.html" rel="nofollow">http://matplotlib.org/examples/index.html</a> 
You can generate prety diagrams too.</p>

<p>I am generating my reports from generated latex files. But there are many pdf libraries for python too. </p>

<p>It's only one way to get it done.</p>

python mysql anaconda spyder