Python 使用核心 SQLAlchemy 插入和更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21206869/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert and update with core SQLAlchemy
提问by Jester
I have a database that I don't have metadata or orm classes for (the database already exists).
我有一个没有元数据或 orm 类的数据库（数据库已经存在）。
I managed to get the select stuff working by:
我设法让选择的东西通过以下方式工作：
from sqlalchemy.sql.expression import ColumnClause
from sqlalchemy.sql import table, column, select, update, insert
from sqlalchemy.ext.declarative import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
import pyodbc
db = create_engine('mssql+pyodbc://pytest')
Session = sessionmaker(bind=db)
session = Session()
list = []
list.append (column("field1"))
list.append (column("field2"))
list.append (column("field3"))
s = select(list)
s.append_from('table')
s.append_whereclause("field1 = 'abc'")
s = s.limit(10)
result = session.execute(s)
out = result.fetchall()
print(out)
So far so good.
到现在为止还挺好。
The only way I can get an update/insert working is by executing a raw query like:
我可以让更新/插入工作的唯一方法是执行一个原始查询，如：
session.execute(<Some sql>)
I would like to make it so I can make a class out of that like:
我想这样做，这样我就可以制作一个类，例如：
u = Update("table")
u.Set("file1","some value")
u.Where(<some conditon>)
seasion.execute(u)
Tried (this is just one of the approaches I tried):
尝试过（这只是我尝试过的方法之一）：
i = insert("table")
v = i.values([{"name":"name1"}, {"name":"name2"}])
u = update("table")
u = u.values({"name": "test1"})
I can't get that to execute on:
我无法执行：
session.execute(i)
or
或者
session.execute(u)
Any suggestion how to construct an insert or update without writing ORM models?
任何建议如何在不编写 ORM 模型的情况下构建插入或更新？
采纳答案by van
As you can see from the SQLAlchemy Overviewdocumentation, sqlalchemy is build with two layers: ORMand Core. Currently you are using only some constructs of the Coreand building everything manually.
正如您从SQLAlchemy 概述文档中看到的，sqlalchemy 由两层构建：ORM和Core. 目前，您仅使用 的一些构造Core并手动构建所有内容。
In order to use Coreyou should let SQLAlchemy know some meta information about your database in order for it to operate on it. Assuming you have a table mytablewith columns field1, field2, field3and a defined primary key, the code below should perform all the tasks you need:
为了使用Core你应该让 SQLAlchemy 知道一些关于你的数据库的元信息，以便它对其进行操作。假设您有一个mytable包含列field1, field2, field3和定义的表，primary key下面的代码应该执行您需要的所有任务：
from sqlalchemy.sql import table, column, select, update, insert
# define meta information
metadata = MetaData(bind=engine)
mytable = Table('mytable', metadata, autoload=True)
# select
s = mytable.select() # or:
#s = select([mytable]) # or (if only certain columns):
#s = select([mytable.c.field1, mytable.c.field2, mytable.c.field3])
s = s.where(mytable.c.field1 == 'abc')
result = session.execute(s)
out = result.fetchall()
print(out)
# insert
i = insert(mytable)
i = i.values({"field1": "value1", "field2": "value2"})
session.execute(i)
# update
u = update(mytable)
u = u.values({"field3": "new_value"})
u = u.where(mytable.c.id == 33)
session.execute(u)
