Home Php C# Sql C C++ Javascript Python Java Go Android Git Linux Asp.net Django .net Node.js Ios Xcode Cocoa Iphone Mysql Tomcat Mongodb Bash Objective-c Scala Visual-studio Apache Elasticsearch Jar Eclipse Jquery Ruby-on-rails Ruby Rubygems Android-studio Spring Lua Sqlite Emacs Ubuntu Perl Docker Swift Amazon-web-services Svn Html Ajax Xml Java-ee Maven Intellij-idea Rvm Macos Unix Css Ipad Postgresql Css3 Json Windows-server Vue.js Typescript Oracle Hibernate Internet-explorer Github Tensorflow Laravel Symfony Redis Html5 Google-app-engine Nginx Firefox Sqlalchemy Lucene Erlang Flask Vim Solr Webview Facebook Zend-framework Virtualenv Nosql Ide Twitter Safari Flutter Bundle Phonegap Centos Sphinx Actionscript Tornado Register | Login | Edit Tags | New Questions | 繁体 | 简体


8 questions online user: 6

0
votes
answers
88 views
+10

SQLAlchemy LazyLoading如何工作

1

您好我想了解sqlalchemy懶加載是如何工作的?假設我有這個疑問SQLAlchemy LazyLoading如何工作

results = (
     session.query(Parent). 
      options(lazyload(Parent.children)). 
      filter(Parent.id == 1). 
      all() 
    ) 

    for parent in results: 
     logging.error(parent.children) 

我想知道,如果我訪問了該parent.children for循環將在創建一個新的select語句?或者是已經被緩存的記錄或parent.children或什麼?我正在考慮這將如何影響性能。我只是想最優化的方式。

  1. 我應該使用lazyloading嗎?
  2. 訪問循環中的每個項目是否會創建新的sqlalchemy
  3. 如何查看sqlalchemy是否正在運行查詢? (只是想看看每個條目訪問將創建一個select語句
沙发
0
1
  1. 可能。
  2. 你的意思是發出一個新的查詢呢?答案是yes,這就是lazyload()點。關係集合屬性在第一次訪問時會被填充,如果另一方面您希望避免可能出現的N + 1情況,您可以使用joinedload()來代替相同查詢中的子項。在您的引擎配置中通過echo=True
0
votes
answers
86 views
+10

flask - sqlalchemy - 自引用查詢

0

我嘗試在sqlalchemy中進行查詢以獲取在父級和子級上過濾的自引用關係。flask - sqlalchemy - 自引用查詢

category_country = Table('category_country', Base.metadata, 
    Column('category_id', Integer, ForeignKey('category.id'), primary_key=True), 
    Column('country_id', Integer, ForeignKey('country.id'), primary_key=True) 
) 

class Category(Base): 
    __tablename__ = "category" 
    id = Column(Integer, primary_key=True, autoincrement=True) 
    parent_id = Column(Integer, ForeignKey('category.id')) 
    subcategories = relationship("Category", backref=backref('parent', remote_side=id)) 
    countries = relationship(Country, secondary = category_country, backref='categories') 

class Country(Base): 
    __tablename__ = "country" 
    id = Column(Integer, primary_key=True) 

查詢

category = s.query(Category).join(Category.countries).options(contains_eager(Category.countries)).filter(Country.id == 1).filter(Category.id == category_id).join(Category.countries, aliased=True).join(Category.subcategories, aliased=True).options(contains_eager(Category.countries)).filter(Country.id == 1).first() 

,但它不工作。我需要找到孩子這是從國家1和其父是CATEGORY_ID和國家也是1

沙发
0
0

我沒有完全得到我的第一次讀你的模型/代碼,但我會解決這個的辦法是通過拆分自我指涉加入到子查詢()語句這樣的:

filter_by_country = (db.session.query(...) 
        .filter(...) 
        .subquery()) 

final_results = (db.session.query(...) 
       .join(filter_by_country, 
         db.and_(Category.id == filter_by_country.c.id, ..., ...)) 
       .options(...) 
       .filter(...) 
       .etc(...).first()) 

我發現這種模式可以幫助簡化這些類型的查詢。希望這可以幫助。

0
votes
answers
92 views
+10

如何設置SQLAlchemy中兩個表之間的關係?

0

我有兩個表:如何設置SQLAlchemy中兩個表之間的關係?

  1. 公告
  2. AnnouncementsSchedule

關係是關鍵one(Announcements)many(AnnouncementsSchedule)

Announcements.id = AnnouncementsSchedule.announcements_id 

我試圖描述SQLAlchemy的機型:

第一個表被描述爲模型:

class Announcements(db.Model): 
    __tablename__ = 'announcements' 
    id = db.Column(db.Integer, primary_key=True) 
    name = db.Column(db.String(150), nullable=False) 
    text = db.Column(db.Text(), nullable=False) 
    category = db.Column(db.Integer(), nullable=False) 
    subcategory = db.Column(db.Integer(), nullable=False) 
    offer_type = db.Column(db.Integer(), nullable=False) 
    url = db.Column(db.String(150), nullable=True) 
    status = db.Column(db.Integer(), nullable=False) 
    #children = relationship("AnnouncementsSchedule", back_populates="announcements") 

二是:

class AnnouncementsSchedule(db.Model): 
    __tablename__ = 'announcements_schedule' 
    id = Column(Integer, primary_key=True) 
    week_day = db.Column(db.Integer(), nullable=True) 
    week_all = db.Column(db.Integer(), nullable=False) 
    time = db.Column(db.Time(), nullable=False) 
    announcement_id = Column(Integer, ForeignKey('announcements.announcements_id')) 

我做什麼了?

+0

你有沒有一個得到錯誤?你有什麼問題? –

沙发
0
0

你有列名錯誤(公告沒有announcement_id):

# announcement_id = Column(Integer, ForeignKey('announcements.announcements_id')) 
# change to -> 
announcement_id = Column(Integer, ForeignKey('announcements.id')) 
+0

謝謝,那麼如何從相關表中獲取數據? – Jessie

+0

announcement = relationship(「Announcements」,back_populates =「schedule」) - 在第一個模型上,put - > schedules = relationship(「AnnouncementsSchedule」,back_populates =「announcement」) –

0
votes
answers
83 views
+10

急切地加載一個循環關聯

0

我以下是多對多關聯的一個簡單例子。我的目標是要加載的X單個記錄,也急切地加載它們在記錄的ys列表以及這是在任何情況下這些公司xs名單的X實例的Y的實例。急切地加載一個循環關聯

class X(db.Model): 
    __tablename__ = 'x' 
    xid = db.Column(db.Integer, primary_key=True) 
    ys = relationship('Z', back_populates='x', lazy='joined') 


class Y(db.Model): 
    __tablename__ = 'y' 
    yid = db.Column(db.Integer, primary_key=True) 
    xs = relationship('Z', back_populates='y', lazy='joined') 


class Z(db.Model): 
    __tablename__ = 'z' 
    xid = db.Column(db.Integer, db.ForeignKey('x.xid'), primary_key=True) 
    yid = db.Column(db.Integer, db.ForeignKey('y.yid'), primary_key=True) 
    x = relationship('X', back_populates='ys', lazy='joined') 
    y = relationship('Y', back_populates='xs', lazy='joined') 

我的目標是產生如下結果:

expected = [{ 
    'xid': 1, 
    'ys': [ 
     {'yid': 101, 'xs': [{'xid': 1}, {'xid': 2}, {'xid': 3}]}, 
     {'yid': 102, 'xs': [{'xid': 1}, {'xid': 2}]}, 
     {'yid': 104, 'xs': [{'xid': 1}, {'xid': 4}]}, 
    ], 
}] 

的SQL語句來實現,這是相當簡單:

SELECT x.xid, y.yid, x2.xid FROM x 
JOIN z  ON z.xid = x.xid JOIN y  ON z.yid = y.yid ; Fetch Ys 
JOIN z as z2 ON z2.yid = y.yid JOIN x as x2 ON z2.xid = x2.xid ; Fetch Xs (depth 2) 
WHERE x.xid = 1 

我的問題是確定如何創建SQLAlchemy的查詢(a)允許我執行這個原始查詢並將其正確映射到正確的模型實例,或者(b)按照查詢(使用join和contains_eager調用的某種組合s),以便它知道如何使用生成的連接,以便它不會分解爲n + 1個查詢。

正確的查詢是由以下生成的,但我無法設法從這個查詢中加載深度2個X實例(數據是由第二個選擇器懶洋洋地加載的)。

a = aliased(Z) 
b = aliased(X) 
q = X.query.filter(X.xid==1).join(X.ys).join(Z.y).join(a, Y.xs).join(b, Z.x) 
沙发
0
4

熱切加載機制的工作方式是,你需要指定要和加載,只要你想如何加載它的關係路徑。路徑基本上是按順序跟隨的關係,以便找到你想要的關係。在您的具體的例子,做正確的事情是這樣的:

q = session.query(X).filter(X.xid == 1)  
      .join(X.ys)  
      .join(Z.y)  
      .join(a, Y.xs)  
      .join(b, Z.x)  
      .options(
       contains_eager(X.ys), 
       contains_eager(X.ys, Z.y), 
       contains_eager(X.ys, Z.y, Y.xs, alias=a), 
       contains_eager(X.ys, Z.y, Y.xs, Z.x, alias=b), 
      ) 

每個contains_eager指定單個關係的負荷,與指定路徑(X.ys, Z.y, Y.xs, Z.x)式的關係,和contains_eager以及alias指定如何加載關係。這是相當冗長,但幸運的SQLAlchemy提供了一條捷徑,讓你把它們連在一起,就像這樣:

.options(contains_eager(X.ys).contains_eager(Z.y).contains_eager(Y.xs, alias=a).contains_eager(Z.x, alias=b)) 

如果您使用.join對於然後做contains_eager明確的目標,你可能也只是使用joinedload而不是:

q = session.query(X).filter(X.xid==1)  
      .options(joinedload(X.ys).joinedload(Z.y).joinedload(Y.xs).joinedload(Z.x)) 

你的具體情況,加入這樣可以不,如果你的分支系數高是有效的,也就是說,如果你的X.ysY.xs包含最多n條目,那麼你的數據庫中有你發副本X中的每一行都是。出於這個原因,subqueryload通常是一對多關係的正確選擇(情況並非總是如此;折衷在查詢的數量即延遲與每個查詢中的數據量(即吞吐量)之間,所以輪廓找出):

q = session.query(X).filter(X.xid==1)  
      .options(subqueryload(X.ys).joinedload(Z.y).subqueryload(Y.xs).joinedload(Z.x)) 

最後,如果你想要的是一個多一對多的關係,爲什麼不直接配置在首位很多一對多的關係?

+0

太棒了 - 我的問題是,我只使用_last_'contains_eager'調用,你在第一個例子中。我認爲這足以對路徑進行編碼。 – efritz

0
votes
answers
99 views
+10

如何通過調用Postgres函數插入記錄時提交

0

我寫了一個函數將記錄插入表person。行ID是串行(自動遞增)如何通過調用Postgres函數插入記錄時提交

CREATE OR REPLACE FUNCTION public.add_person(
    name character varying, 
    email character varying, 
    level integer, 
    company_id integer, 
    comp_list integer[]) 
    RETURNS integer as 

$BODY$ 
declare 
    p_id integer; 
begin 
    insert into person (name, email, level, company_id, comp_list, status, insert_ts) values ($1, $2, $3, $4, $5, 'Y', now()) 
    returning person.person_id into p_id; 
    return p_id; 
end 
$BODY$ LANGUAGE 'plpgsql' 

如果我運行SQL select * from add_person('xxx', '[email protected]', 1, 3, '{1,2}')這個功能,它成功地插入一條記錄。但是,當我使用SQLAlchemy在Python中調用此函數時,無法插入記錄。

engine = create_engine(URL(**settings.DATABASE)) 
session = scoped_session(sessionmaker(bind=engine)) 
email = '[email protected]' 
company = 1 
level = 3 
comp_list = '1,2' 
args = "'', '" + email + "', " + str(company) + ", " + str(level) + ", '{" + comp_list + "}'" 
statement = "select * from add_person({})".format(args) 
session.execute(statement) 

在Python中構造的語句與我在postgres中運行的命令完全相同。但它並沒有像表中那樣插入記錄。根本沒有錯誤信息。 session,engine配置正確,因爲所有其他select查詢工作。

我也注意到,即使記錄不能使用python代碼插入。主鍵的順序確實增加了。因爲當我在postgres中再次運行函數時,行ID跳過了。

如果我用SQLAlchemy會話進行插入而沒有提交,行爲是一樣的。

def add_person(name, email, company, level, comp_list): 
    current_ts = datetime.datetime.now() 
    p = Person(name = name, 
       email = email, 
       company_id = company, 
       level = level, 
       comp_list = comp_list, 
       status = 'Y', 
       insert_ts = current_ts) 
    session.add(p) 
    session.flush() 
    return p.person_id 

如果我運行上述方法的Python,person_id增量但沒有記錄被插入。只有當我將session.flush更改爲以下時,記錄才能正確插入。

session.add(p) 
    session.commit() 
    session.refresh(p) 
    print(p.person_id) 
    return p.person_id 

調用plsql函數時提交插入操作的正確方法是什麼?

+1

嘗試'conn.autocommit = True'請參閱[事務控制。](http://initd.org/psycopg/docs/usage.html#transactions-control) – klin

+0

請*不要使用字符串格式*來傳遞值查詢。這就是佔位符/綁定對象的用途。閱讀http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-textual-sql查看「how」和https://xkcd.com/327/查看「why」。 –

+1

這裏甚至不需要文本SQL。函數泛型允許你執行'func.add_person('',email,company,level,comp_list.split(','))。select())'。請注意,您可以並應該將comp_list作爲一個列表。 –

沙发
0
1

除非您有autocommit連接設置爲True,否則您需要調用會話的commit()函數。

文檔:http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it


的原因,你的person_id增加,即使沒有提交,是因爲它使用的是序列 - 無論你創建了明確,或者爲您創建當你定義的列類型SERIAL (或類似)。該序列會在有或沒有提交的情況下遞增,並且不依賴於在表中成功插入記錄。

請注意,只涉及SELECT的語句不需要調用提交。

0
votes
answers
113 views
+10

如何使用SQLAlchemy/Mysql存儲3D圖形(x,y,z)數據?

0

我正在使用Python的Flask將3D圖形的X,Y,Z值賦給Javascript(Plotly庫)。數據是這樣的:如何使用SQLAlchemy/Mysql存儲3D圖形(x,y,z)數據?

z = [1,1,1,1,1,1,1,1,1,1,1,1]; 
x = [0.0100,0.0420,0.0500,0.0508,0.0769,0.0883,0.0900,0.1101,0.1256,0.1300,0.1385,0.1607]; 
y = [0.0131,0.0190,0.0248,0.0250,0.0310,0.0370,0.0384,0.0430,0.0490,0.0530,0.0550,0.0610]; 

所有這些都爲1個的3D圖形和它看起來像這樣: enter image description here

問題是,我會在我的網站很多3D圖形和他們都儲存在代碼中的Python數組很荒謬。將所有這些座標存儲在數據庫中最有效的方法是什麼?謝謝。

+0

您可以將這些變量轉換爲JSON並將其轉換成字符串,並將其存儲到數據庫,但是你需要再次轉換回得到時字符串值。 –

沙发
0
1

您可以使用PostgreSQL數據庫,這是有數組的,所以,你可以創建一個表,你會包含列:

id: integer 
graph_name: text 
xpoints: double precision[] 
ypoints: double precision[] 
zpoints: double precision[] 

其中,「雙精度[]」是8個字節的float數組數據類型。

所以每個圖都會存儲在表的一行中。

對於數組列,你應該使用postgresql.ARRAY數據類型(在SQLAlchemy中)

+0

謝謝,我會看看它 –

0
votes
answers
111 views
+10

爲什麼SQLAlchemy關聯對象中的外鍵標記爲主鍵?

0

以下是sqlalchemy的文檔。爲什麼SQLAlchemy關聯對象中的外鍵標記爲主鍵?

注意如何在關聯類left_id和right_id,他們 第一標記爲ForeignKey的,然後primary_key =真

這是有道理的,我認爲他們應該是外鍵,因爲邏輯上它們是外鍵的其他兩張父母和孩子的桌子。

那麼,讓它們成爲主鍵的目的是什麼呢?

這是怎麼回事?請解釋。

class Association(Base): 
    __tablename__ = 'association' 
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True) 
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True) 
    extra_data = Column(String(50)) 
    child = relationship("Child", back_populates="parents") 
    parent = relationship("Parent", back_populates="children") 

class Parent(Base): 
    __tablename__ = 'left' 
    id = Column(Integer, primary_key=True) 
    children = relationship("Association", back_populates="parent") 

class Child(Base): 
    __tablename__ = 'right' 
    id = Column(Integer, primary_key=True) 
    parents = relationship("Association", back_populates="child") 
沙发
0
0

這不是SQLAlchemy特有的。這就是如何設計many-to-many relationships,這是基於關係數據庫設計的原則。

在多對多關係中,需要一個附加表,也稱爲關聯表,它將第一個表中的條目與第二個表中的對應條目進行映射。

當關聯表被定義時,我們需要一些主鍵來唯一標識關聯表中的記錄。使用主鍵可創建索引,從而加快聯合操作和搜索記錄。

那麼,爲什麼所有的外鍵都作爲關聯表的主鍵的一部分呢? 這是爲了確保沒有重複條目atable A和記錄bTable B。換句話說,要確保關係的唯一性,從而避免重複關係。

可以在不將外鍵聲明爲主鍵的情況下創建關聯表。但這是不建議。通過這樣做,除非明確創建索引,否則聯接操作變得緩慢。而且,有很好的機會可以重複記錄Table ATable B之間的關係

104
votes
answers
68 views
+10

如何讓Tornado中的SQLAlchemy異步?

如何使SQLAlchemy中Tornado是async?我在異步mongo示例中找到了MongoDB的示例,但我找不到類似motor的內容SQLAlchemy。有沒有人知道如何SQLAlchemy執行查詢tornado.gen(我在MySQL下面使用SQLAlchemy,目前我的處理程序從數據庫讀取並返回結果,我想使這個異步)。
up vote 74 down vote accepted favorite
沙发
+740
+50

ORMs are poorly suited for explicit asynchronous programming, that is, where the programmer must produce explicit callbacks anytime something that uses network access occurs. A primary reason for this is that ORMs make extensive use of the lazy loading pattern, which is more or less incompatible with explicit async. Code that looks like this:

user = Session.query(User).first()
print user.addresses

will actually emit two separate queries - one when you say first() to load a row, and the next when you say user.addresses, in the case that the .addresses collection isn't already present, or has been expired. Essentially, nearly every line of code that deals with ORM constructs might block on IO, so you'd be in extensive callback spaghetti within seconds - and to make matters worse, the vast majority of those code lines won't actually block on IO, so all the overhead of connecting callbacks together for what would otherwise be simple attribute access operations will make your program vastly less efficient too.

A major issue with explicit asynchronous models is that they add tremendous Python function call overhead to complex systems - not just on the user-facing side like you get with lazy loading, but on the internal side as well regarding how the system provides abstraction around the Python database API (DBAPI). For SQLAlchemy to even have basic async support would impose a severe performance penalty on the vast majority of programs that don't use async patterns, and even those async programs that are not highly concurrent. Consider SQLAlchemy, or any other ORM or abstraction layer, might have code like the following:

def execute(connection, statement):
     cursor = connection.cursor()
     cursor.execute(statement)
     results = cursor.fetchall()
     cursor.close()
     return results

The above code performs what seems to be a simple operation, executing a SQL statement on a connection. But using a fully async DBAPI like psycopg2's async extension, the above code blocks on IO at least three times. So to write the above code in explicit async style, even when there's no async engine in use and the callbacks aren't actually blocking, means the above outer function call becomes at least three function calls, instead of one, not including the overhead imposed by the explicit asynchronous system or the DBAPI calls themselves. So a simple application is automatically given a penalty of 3x the function call overhead surrounding a simple abstraction around statement execution. And in Python, function call overhead is everything.

For these reasons, I continue to be less than excited about the hype surrounding explicit async systems, at least to the degree that some folks seem to want to go all async for everything, like delivering web pages (see node.js). I'd recommend using implicit async systems instead, most notably gevent, where you get all the non-blocking IO benefits of an asynchronous model and none of the structural verbosity/downsides of explicit callbacks. I continue to try to understand use cases for these two approaches, so I'm puzzled by the appeal of the explicit async approach as a solution to all problems, i.e. as you see with node.js - we're using scripting languages in the first place to cut down on verbosity and code complexity, and explicit async for simple things like delivering web pages seems to do nothing but add boilerplate that can just as well be automated by gevent or similar, if blocking IO is even such a problem in a case like that (plenty of high volume websites do fine with a synchronous IO model). Gevent-based systems are production proven and their popularity is growing, so if you like the code automation that ORMs provide, you might also want to embrace the async-IO-scheduling automation that a system like gevent provides.

Update: Nick Coghlan pointed out his great article on the subject of explicit vs. implicit async which is also a must read here. And I've also been updated to the fact that pep-3156 now welcomes interoperability with gevent, reversing its previously stated disinterest in gevent, largely thanks to Nick's article. So in the future I would recommend a hybrid of Tornado using gevent for the database logic, once the system of integrating these approaches is available.

自這個答案發布以來已經過去了4年。我想知道,zzzeek,或社區中的任何其他人。他們對ORM環境有什麼重大改變,Tornado或gevent是否需要對此答案進行審核?我問,因為我可能在這裡面臨這個問題:stackoverflow.com/questions/44599402 / ... Bokeh服務器應用程序使用Tornado,我需要連接到數據庫以從中檢索信息。 - Thornhale 17年6月17日14:23

一如既往的答案......業務和持久性邏輯不需要非阻塞IO。如果您被迫使用強制所有內容“顯式異步”的Web框架,那麼最佳解決方案是a)。使用普通的Web框架,如燒瓶或金字塔或b。使用線程池 - zzzeek 17年6月17日在19:56

+240

I had this same issue in the past and I couldn't find a reliable Async-MySQL library. However there is a cool solution using Asyncio + Postgres. You just need to use the aiopg library, which comes with SQLAlchemy support out of the box:

import asyncio
from aiopg.sa import create_engine
import sqlalchemy as sa


metadata = sa.MetaData()

tbl = sa.Table('tbl', metadata,
           sa.Column('id', sa.Integer, primary_key=True),
           sa.Column('val', sa.String(255)))

@asyncio.coroutine
def go():
    engine = yield from create_engine(user='aiopg',
                                      database='aiopg',
                                      host='127.0.0.1',
                                      password='passwd')

    with (yield from engine) as conn:
        yield from conn.execute(tbl.insert().values(val='abc'))

        res = yield from conn.execute(tbl.select().where(tbl.c.val=='abc'))
        for row in res:
            print(row.id, row.val)


loop = asyncio.get_event_loop()
loop.run_until_complete(go())
+40

Not tornado, but we sort of made SQLAlchemy async in asyncio in the GINO project:

import asyncio
from gino import Gino, enable_task_local
from sqlalchemy import Column, Integer, Unicode, cast

db = Gino()


class User(db.Model):
    __tablename__ = 'users'

    id = Column(Integer(), primary_key=True)
    nickname = Column(Unicode(), default='noname')


async def main():
    await db.create_pool('postgresql://localhost/gino')

    # Create object, `id` is assigned by database
    u1 = await User.create(nickname='fantix')
    print(u1.id, u1.nickname)  # 1 fantix

    # Retrieve the same row, as a different object
    u2 = await User.get(u1.id)
    print(u2.nickname)  # fantix

    # Update affects only database row and the operating object
    await u2.update(nickname='daisy')
    print(u2.nickname)  # daisy
    print(u1.nickname)  # fantix

    # Returns all user objects with "d" in their nicknames
    users = await User.query.where(User.nickname.contains('d')).gino.all()

    # Find one user object, None if not found
    user = await User.query.where(User.nickname == 'daisy').gino.first()

    # Execute complex statement and return command status
    status = await User.update.values(
        nickname='No.' + cast(User.id, Unicode),
    ).where(
        User.id > 10,
    ).gino.status()

    # Iterate over the results of a large query in a transaction as required
    async with db.transaction():
        async for u in User.query.order_by(User.id).gino.iterate():
            print(u.id, u.nickname)


loop = asyncio.get_event_loop()
enable_task_local(loop)
loop.run_until_complete(main())

It looks a bit like, but actually quite different than SQLAlchemy ORM. Because we used only a part of SQLAlchemy core, and built a simple ORM on top of it. It uses asyncpg underneath, so it is for PostgreSQL only.

Update: GINO supports Tornado now, thanks to the contribution of Vladimir Goncharov. See docs here

+20

I am using tornado with sqlalchemy in next way:


from tornado_mysql import pools
from sqlalchemy.sql import table, column, select, join
from sqlalchemy.dialects import postgresql, mysql

# from models import M, M2

t = table(...)
t2 = table(...)

xxx_id = 10

j = join(t, t2, t.c.t_id == t2.c.id)
s = select([t]).select_from(j).where(t.c.xxx == xxx_id)

sql_str = s.compile(dialect=mysql.dialect(),compile_kwargs={"literal_binds": True})


pool = pools.Pool(conn_data...)
cur = yield pool.execute(sql_str)
data = cur.fetchone()

In that case we are able to use sqlalchemy models, and sqlalchemy tools for constructig queries.