SQLAlchemy - simple select statement

Question

Background:

I am very new to SQLAlchemy and it seems to be fairly confusing as to how I should be selecting things.

I have a table in my mysql database which is called genes, where I have gene_id, gene_description, and gene_symbol

What I want to do:

All I want to do is a simple select query:

Select * from Genes

But I seem to be confused as to how we shall achieve this

Here is what I have done:

import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.automap import automap_base
import csv
import pandas as pd

engine = sqlalchemy.create_engine('mysql://root:toor@localhost') # connect to server
metadata = sqlalchemy.MetaData(bind=engine)
engine.execute("USE TestDB")

genes = sqlalchemy.table('Genes')
s = sqlalchemy.select([genes])
engine.execute(s)

The problem:

ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `Genes`' at line 2") [SQL: u'SELECT  \nFROM `Genes`']

Also is there some type of "intellisense" where I can just do something like gene_table = engine.Gene. If I am not mistake there is a way to do this with mapping but it didn't work for me

EDIT: This may help:

How to automatically reflect database to sqlalchemy declarative?

Answer 1

So we can use reflection and do not have to create classes explicitly, but if we want speed we can create them using something like sqlautocode as stated here:

Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?

Also there is an issue with mysql databases where it will give an error that looks like the following: (taken from bitbucket: https://bitbucket.org/zzzeek/sqlalchemy/issues/1909/reflection-issue-with-mysql-url-with-no)

SNIP...
File "/opt/buildout-eggs/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/schema.py", line 1927, in __init__
    self.reflect()
  File "/opt/buildout-eggs/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/schema.py", line 2037, in reflect
    connection=conn))
  File "/opt/buildout-eggs/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/engine/base.py", line 1852, in table_names
    return self.dialect.get_table_names(conn, schema)
  File "<string>", line 1, in <lambda>
  File "/opt/buildout-eggs/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/engine/reflection.py", line 32, in cache
    return fn(self, con, *args, **kw)
  File "/opt/buildout-eggs/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/dialects/mysql/base.py", line 1791, in get_table_names
    self.identifier_preparer.quote_identifier(current_schema))
  File "/opt/buildout-eggs/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/sql/compiler.py", line 1517, in quote_identifier
    return self.initial_quote + self._escape_identifier(value) + self.final_quote
  File "/opt/buildout-eggs/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/dialects/mysql/mysqldb.py", line 77, in _escape_identifier
    value = value.replace(self.escape_quote, self.escape_to_quote)
AttributeError: 'NoneType' object has no attribute 'replace'

This is resolved by adding a database name (the one you are using) as follows:

engine = create_engine('mysql+mysqldb://USER_NAME:[email protected]/DATABASE_NAME', pool_recycle=3600) # connect to server

I used this to connect correctly: http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html and this: http://docs.sqlalchemy.org/en/latest/core/engines.html

This also may help: How to automatically reflect database to sqlalchemy declarative?

My code finally looks like this:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()

# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine('mysql+mysqldb://root:[email protected]/TestDB', pool_recycle=3600) # connect to server

# reflect the tables
Base.prepare(engine, reflect=True)

# mapped classes are now created with names by default
# matching that of the table name.
Genes = Base.classes.Genes

Address = Base.classes.address
#Start Session
session = Session(engine)
#add a row:
session.add(Genes(Gene_Id=1,Gene_Symbol = "GENE_SYMBOL", Gene_Description="GENE_DESCRIPTION"))
session.commit()
q = session.query(Genes).all()
for gene in q:
    print "This is the Gene ID {},\n This is the Gene Desc {},\n this is the Gene symbol {}.".format(gene.Gene_Id,gene.Gene_Description, gene.Gene_Symbol )