python-SQLAlchemy-简单的选择语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了python-SQLAlchemy-简单的选择语句,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3931字,纯文字阅读大概需要6分钟。
内容图文
背景:
我是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?
解决方法:
因此,我们可以使用反射,而不必显式创建类,但是如果我们想提高速度,可以使用如下所示的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 )
内容总结
以上是互联网集市为您收集整理的python-SQLAlchemy-简单的选择语句全部内容,希望文章能够帮你解决python-SQLAlchemy-简单的选择语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。