def test_int_default_none_on_insert_reflected(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer,
server_default='5', primary_key=True),
Column('data', String(10)),
implicit_returning=False
)
metadata.create_all()
m2 = MetaData(metadata.bind)
t2 = Table('x', m2, autoload=True, implicit_returning=False)
r = t2.insert().execute(data='data')
eq_(r.inserted_primary_key, [None])
if testing.against('sqlite'):
eq_(
t2.select().execute().fetchall(),
[(1, 'data')]
)
else:
eq_(
t2.select().execute().fetchall(),
[(5, 'data')]
)
def test_reflect(self):
t1.insert().execute({u'méil':2, u'\u6e2c\u8a66':7})
t2.insert().execute({'a':2, 'b':2})
t3.insert().execute({u'\u6e2c\u8a66_id': 2,
u'unitable1_\u6e2c\u8a66': 7,
u'Unitéble2_b': 2,
u'\u6e2c\u8a66_self': 2})
meta = MetaData(unicode_bind)
tt1 = Table(t1.name, meta, autoload=True)
tt2 = Table(t2.name, meta, autoload=True)
tt3 = Table(t3.name, meta, autoload=True)
tt1.insert().execute({u'méil':1, u'\u6e2c\u8a66':5})
tt2.insert().execute({u'méil':1, u'\u6e2c\u8a66':1})
tt3.insert().execute({u'\u6e2c\u8a66_id': 1,
u'unitable1_\u6e2c\u8a66': 5,
u'Unitéble2_b': 1,
u'\u6e2c\u8a66_self': 1})
self.assert_(tt1.select(order_by=desc(u'méil')).execute().fetchall() ==
[(2, 7), (1, 5)])
self.assert_(tt2.select(order_by=desc(u'méil')).execute().fetchall() ==
[(2, 2), (1, 1)])
self.assert_(tt3.select(order_by=desc(u'\u6e2c\u8a66_id')).
execute().fetchall() ==
[(2, 7, 2, 2), (1, 5, 1, 1)])
meta.drop_all()
metadata.create_all()
def test_int_default_none_on_insert_reflected(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer,
server_default='5', primary_key=True),
Column('data', String(10)),
implicit_returning=False
)
metadata.create_all()
m2 = MetaData(metadata.bind)
t2 = Table('x', m2, autoload=True, implicit_returning=False)
r = t2.insert().execute(data='data')
eq_(r.inserted_primary_key, [None])
if testing.against('sqlite'):
eq_(
t2.select().execute().fetchall(),
[(1, 'data')]
)
else:
eq_(
t2.select().execute().fetchall(),
[(5, 'data')]
)
def test_int_default_none_on_insert(self):
metadata = self.metadata
t = Table('x',
metadata,
Column('y', Integer, server_default='5', primary_key=True),
Column('data', String(10)),
implicit_returning=False)
assert t._autoincrement_column is None
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, [None])
if testing.against('sqlite'):
eq_(t.select().execute().fetchall(), [(1, 'data')])
else:
eq_(t.select().execute().fetchall(), [(5, 'data')])
def test_custom_subclass_proxy(self):
"""test proxy generation of a Column subclass, can be compiled."""
from sqlalchemy.schema import Column
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import select
class MyColumn(Column):
def _constructor(self, name, type, **kw):
kw['name'] = name
return MyColumn(type, **kw)
def __init__(self, type, **kw):
Column.__init__(self, type, **kw)
def my_goofy_thing(self):
return "hi"
@compiles(MyColumn)
def goofy(element, compiler, **kw):
s = compiler.visit_column(element, **kw)
return s + "-"
id = MyColumn(Integer, primary_key=True)
id.name = 'id'
name = MyColumn(String)
name.name = 'name'
t1 = Table('foo', MetaData(),
id,
name
)
# goofy thing
eq_(t1.c.name.my_goofy_thing(), "hi")
# create proxy
s = select([t1.select().alias()])
# proxy has goofy thing
eq_(s.c.name.my_goofy_thing(), "hi")
# compile works
self.assert_compile(
select([t1.select().alias()]),
"SELECT anon_1.id-, anon_1.name- FROM "
"(SELECT foo.id- AS id, foo.name- AS name "
"FROM foo) AS anon_1",
)
def _run_test(self, *arg, **kw):
metadata = self.metadata
implicit_returning = kw.pop('implicit_returning', True)
kw['primary_key'] = True
if kw.get('autoincrement', True):
kw['test_needs_autoincrement'] = True
t = Table('x', metadata,
Column('y', self.MyInteger, *arg, **kw),
Column('data', Integer),
implicit_returning=implicit_returning
)
t.create()
r = t.insert().values(data=5).execute()
# we don't pre-fetch 'server_default'.
if 'server_default' in kw and (not testing.db.dialect.implicit_returning or not implicit_returning):
eq_(r.inserted_primary_key, [None])
else:
eq_(r.inserted_primary_key, ['INT_1'])
r.close()
eq_(
t.select().execute().first(),
('INT_1', 5)
)
def _run_test(self, *arg, **kw):
metadata = self.metadata
implicit_returning = kw.pop('implicit_returning', True)
kw['primary_key'] = True
if kw.get('autoincrement', True):
kw['test_needs_autoincrement'] = True
t = Table('x',
metadata,
Column('y', self.MyInteger, *arg, **kw),
Column('data', Integer),
implicit_returning=implicit_returning)
t.create()
r = t.insert().values(data=5).execute()
# we don't pre-fetch 'server_default'.
if 'server_default' in kw and (
not testing.db.dialect.implicit_returning
or not implicit_returning):
eq_(r.inserted_primary_key, [None])
else:
eq_(r.inserted_primary_key, ['INT_1'])
r.close()
eq_(t.select().execute().first(), ('INT_1', 5))
def _run_test(self, *arg, **kw):
metadata = self.metadata
implicit_returning = kw.pop("implicit_returning", True)
kw["primary_key"] = True
if kw.get("autoincrement", True):
kw["test_needs_autoincrement"] = True
t = Table(
"x",
metadata,
Column("y", self.MyInteger, *arg, **kw),
Column("data", Integer),
implicit_returning=implicit_returning,
)
t.create()
r = t.insert().values(data=5).execute()
# we don't pre-fetch 'server_default'.
if "server_default" in kw and (not testing.db.dialect.implicit_returning or not implicit_returning):
eq_(r.inserted_primary_key, [None])
else:
eq_(r.inserted_primary_key, ["INT_1"])
r.close()
eq_(t.select().execute().first(), ("INT_1", 5))
def test_int_default_on_insert_with_returning(self):
metadata = self.metadata
t = Table("x", metadata, Column("y", Integer, server_default="5", primary_key=True), Column("data", String(10)))
metadata.create_all()
r = t.insert().execute(data="data")
eq_(r.inserted_primary_key, [5])
eq_(t.select().execute().fetchall(), [(5, "data")])
def test_int_default_none_on_insert(self):
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", Integer, server_default="5", primary_key=True),
Column("data", String(10)),
implicit_returning=False,
)
assert t._autoincrement_column is None
metadata.create_all()
r = t.insert().execute(data="data")
eq_(r.inserted_primary_key, [None])
if testing.against("sqlite"):
eq_(t.select().execute().fetchall(), [(1, "data")])
else:
eq_(t.select().execute().fetchall(), [(5, "data")])
def test_func_embedded_whereclause(self):
"""test can use next_value() in whereclause"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
testing.db.execute(t1.insert(), [{"x": 1}, {"x": 300}, {"x": 301}])
s = Sequence("my_sequence")
eq_(testing.db.execute(t1.select().where(t1.c.x > s.next_value())).fetchall(), [(300,), (301,)])
def test_func_embedded_valuesbase(self):
"""test can use next_value() in values() of _ValuesBase"""
metadata = self.metadata
t1 = Table('t', metadata, Column('x', Integer))
t1.create(testing.db)
s = Sequence("my_sequence")
testing.db.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(testing.db.scalar(t1.select()))
def test_func_embedded_valuesbase(self):
"""test can use next_value() in values() of _ValuesBase"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
s = Sequence("my_sequence")
testing.db.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(testing.db.scalar(t1.select()))
def test_int_default_on_insert_with_returning(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer, server_default='5', primary_key=True),
Column('data', String(10)))
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, [5])
eq_(t.select().execute().fetchall(), [(5, 'data')])
def test_empty_insert(self):
metadata = MetaData(testing.db)
t1 = Table("t1", metadata, Column("is_true", Boolean, server_default=("1")))
metadata.create_all()
try:
result = t1.insert().execute()
eq_(1, select([func.count(text("*"))], from_obj=t1).scalar())
eq_(True, t1.select().scalar())
finally:
metadata.drop_all()
def test_int_default_none_on_insert_reflected(self):
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", Integer, server_default="5", primary_key=True),
Column("data", String(10)),
implicit_returning=False,
)
metadata.create_all()
m2 = MetaData(metadata.bind)
t2 = Table("x", m2, autoload=True, implicit_returning=False)
r = t2.insert().execute(data="data")
eq_(r.inserted_primary_key, [None])
if testing.against("sqlite"):
eq_(t2.select().execute().fetchall(), [(1, "data")])
else:
eq_(t2.select().execute().fetchall(), [(5, "data")])
def test_empty_insert(self):
metadata = MetaData(testing.db)
t1 = Table('t1', metadata,
Column('is_true', Boolean, server_default=('1')))
metadata.create_all()
try:
result = t1.insert().execute()
eq_(1, select([func.count(text('*'))], from_obj=t1).scalar())
eq_(True, t1.select().scalar())
finally:
metadata.drop_all()
def test_string_default_on_insert_with_returning(self):
"""With implicit_returning, we get a string PK default back no problem."""
metadata = self.metadata
t = Table(
'x', metadata,
Column('y', String(10), server_default='key_one',
primary_key=True), Column('data', String(10)))
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, ['key_one'])
eq_(t.select().execute().fetchall(), [('key_one', 'data')])
def test_func_embedded_whereclause(self):
"""test can use next_value() in whereclause"""
metadata = self.metadata
t1 = Table('t', metadata, Column('x', Integer))
t1.create(testing.db)
testing.db.execute(t1.insert(), [{'x': 1}, {'x': 300}, {'x': 301}])
s = Sequence("my_sequence")
eq_(
testing.db.execute(
t1.select().where(t1.c.x > s.next_value())).fetchall(),
[(300, ), (301, )])
def test_int_default_none_on_insert(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer,
server_default='5', primary_key=True),
Column('data', String(10)),
implicit_returning=False
)
assert t._autoincrement_column is None
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, [None])
if testing.against('sqlite'):
eq_(
t.select().execute().fetchall(),
[(1, 'data')]
)
else:
eq_(
t.select().execute().fetchall(),
[(5, 'data')]
)
def test_reflect(self):
t1.insert().execute({u'méil': 2, u'\u6e2c\u8a66': 7})
t2.insert().execute({'a': 2, 'b': 2})
t3.insert().execute({
u'\u6e2c\u8a66_id': 2,
u'unitable1_\u6e2c\u8a66': 7,
u'Unitéble2_b': 2,
u'\u6e2c\u8a66_self': 2
})
meta = MetaData(unicode_bind)
tt1 = Table(t1.name, meta, autoload=True)
tt2 = Table(t2.name, meta, autoload=True)
tt3 = Table(t3.name, meta, autoload=True)
tt1.insert().execute({u'méil': 1, u'\u6e2c\u8a66': 5})
tt2.insert().execute({u'méil': 1, u'\u6e2c\u8a66': 1})
tt3.insert().execute({
u'\u6e2c\u8a66_id': 1,
u'unitable1_\u6e2c\u8a66': 5,
u'Unitéble2_b': 1,
u'\u6e2c\u8a66_self': 1
})
self.assert_(
tt1.select(
order_by=desc(u'méil')).execute().fetchall() == [(2, 7), (1,
5)])
self.assert_(
tt2.select(
order_by=desc(u'méil')).execute().fetchall() == [(2, 2), (1,
1)])
self.assert_(
tt3.select(
order_by=desc(u'\u6e2c\u8a66_id')).execute().fetchall() == [(
2, 7, 2, 2), (1, 5, 1, 1)])
meta.drop_all()
metadata.create_all()
def test_string_default_on_insert_with_returning(self):
"""With implicit_returning, we get a string PK default back no problem."""
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", String(10), server_default="key_one", primary_key=True),
Column("data", String(10)),
)
metadata.create_all()
r = t.insert().execute(data="data")
eq_(r.inserted_primary_key, ["key_one"])
eq_(t.select().execute().fetchall(), [("key_one", "data")])
def test_string_default_on_insert_with_returning(self):
"""With implicit_returning, we get a string PK default back no problem."""
metadata = self.metadata
t = Table('x', metadata,
Column('y', String(10), server_default='key_one', primary_key=True),
Column('data', String(10))
)
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, ['key_one'])
eq_(
t.select().execute().fetchall(),
[('key_one', 'data')]
)
def test_int_default_on_insert_with_returning(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer,
server_default='5', primary_key=True),
Column('data', String(10))
)
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, [5])
eq_(
t.select().execute().fetchall(),
[(5, 'data')]
)
def test_row_c_sequence_check(self):
import csv
import collections
from StringIO import StringIO
metadata = MetaData()
metadata.bind = 'sqlite://'
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40)),
)
users.create()
users.insert().execute(name='Test')
row = users.select().execute().fetchone()
s = StringIO()
writer = csv.writer(s)
# csv performs PySequenceCheck call
writer.writerow(row)
assert s.getvalue().strip() == '1,Test'
def test_string_default_none_on_insert(self):
"""Test that without implicit returning, we return None for
a string server default.
That is, we don't want to attempt to pre-execute "server_default"
generically - the user should use a Python side-default for a case
like this. Testing that all backends do the same thing here.
"""
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", String(10), server_default="key_one", primary_key=True),
Column("data", String(10)),
implicit_returning=False,
)
metadata.create_all()
r = t.insert().execute(data="data")
eq_(r.inserted_primary_key, [None])
eq_(t.select().execute().fetchall(), [("key_one", "data")])
def test_no_rowcount_on_selects_inserts(self):
"""assert that rowcount is only called on deletes and updates.
This because cursor.rowcount can be expensive on some dialects
such as Firebird.
"""
metadata = self.metadata
engine = engines.testing_engine()
metadata.bind = engine
t = Table('t1', metadata,
Column('data', String(10))
)
metadata.create_all()
class BreakRowcountMixin(object):
@property
def rowcount(self):
assert False
execution_ctx_cls = engine.dialect.execution_ctx_cls
engine.dialect.execution_ctx_cls = type("FakeCtx",
(BreakRowcountMixin,
execution_ctx_cls),
{})
try:
r = t.insert().execute({'data': 'd1'}, {'data': 'd2'},
{'data': 'd3'})
eq_(t.select().execute().fetchall(), [('d1', ), ('d2', ),
('d3', )])
assert_raises(AssertionError, t.update().execute, {'data'
: 'd4'})
assert_raises(AssertionError, t.delete().execute)
finally:
engine.dialect.execution_ctx_cls = execution_ctx_cls
def test_custom_subclass_proxy_typeerror(self):
from sqlalchemy.schema import Column
from sqlalchemy.sql import select
class MyColumn(Column):
def __init__(self, type, **kw):
Column.__init__(self, type, **kw)
id = MyColumn(Integer, primary_key=True)
id.name = 'id'
name = MyColumn(String)
name.name = 'name'
t1 = Table('foo', MetaData(),
id,
name
)
assert_raises_message(
TypeError,
"Could not create a copy of this <class "
"'test.sql.test_metadata.MyColumn'> "
"object. Ensure the class includes a _constructor()",
getattr, select([t1.select().alias()]), 'c'
)
def test_string_default_none_on_insert(self):
"""Test that without implicit returning, we return None for
a string server default.
That is, we don't want to attempt to pre-execute "server_default"
generically - the user should use a Python side-default for a case
like this. Testing that all backends do the same thing here.
"""
metadata = self.metadata
t = Table('x',
metadata,
Column('y',
String(10),
server_default='key_one',
primary_key=True),
Column('data', String(10)),
implicit_returning=False)
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, [None])
eq_(t.select().execute().fetchall(), [('key_one', 'data')])
