python - SQLAlchemy - 简单的选择语句

Question

背景:

我是 SQLAlchemy 的新手，对于我应该如何选择东西似乎相当困惑。

我的 mysql 数据库中有一个名为 genes 的表，其中有 gene_id、gene_description 和 gene_symbol

我想做什么:

我只想做一个简单的选择查询:

Select * from Genes

但我似乎对如何实现这一目标感到困惑

这是我所做的:

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)

问题:

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`']

还有某种类型的“智能感知”，我可以在其中执行类似 gene_table = engine.Gene 的操作。如果我没记错的话，有一种方法可以通过映射来做到这一点，但它对我不起作用

编辑:这可能会有所帮助:

How to automatically reflect database to sqlalchemy declarative?

Answer 1

因此我们可以使用反射而不必显式创建类，但如果我们想要速度，我们可以使用类似 sqlautocode 的东西来创建它们，如下所述:

Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?

mysql 数据库也有一个问题，它会给出如下所示的错误:(取自 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'

这可以通过添加数据库名称(您正在使用的数据库名称)来解决，如下所示:

engine = create_engine('mysql+mysqldb://USER_NAME:PASSWORD@127.0.0.1/DATABASE_NAME', pool_recycle=3600) # connect to server

我用它来正确连接: http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html和这个: http://docs.sqlalchemy.org/en/latest/core/engines.html

这也可能有帮助: How to automatically reflect database to sqlalchemy declarative?

我的代码最终看起来像这样:

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:toor@127.0.0.1/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 )