def test_sequence_insert(teng):
"""
How to call nextval in a sqlalchemy insert statement. Also an example of
how to called a stored procedure in an insert statement.
"""
meta = MetaData(teng)
table = Table('table', meta, Column('id', Integer), Column('tx', Integer))
table.create()
with teng.connect() as conn:
conn.execute('create sequence id')
conn.execute('create sequence tx')
conn.execute(table.insert().values(id=func.nextval('id'),
tx=func.nextval('tx')))
conn.execute(table.insert().values(id=func.nextval('id'),
tx=func.nextval('tx')))
result = conn.execute(table.select())
assert [(1, 1), (2, 2)] == list(result)
def _execute_default_attr(query, param, attr_name):
for col in query.table.columns:
attr = getattr(col, attr_name)
if attr and param.get(col.name) is None:
if attr.is_sequence:
param[col.name] = func.nextval(attr.name)
elif attr.is_scalar:
param[col.name] = attr.arg
elif attr.is_callable:
param[col.name] = attr.arg({})
def _execute_default_attr(query, param, attr_name):
for col in query.table.columns:
attr = getattr(col, attr_name)
if attr and param.get(col.name) is None:
if attr.is_sequence:
param[col.name] = func.nextval(attr.name)
elif attr.is_scalar:
param[col.name] = attr.arg
elif attr.is_callable:
param[col.name] = attr.arg({})
class UploadAcct(db.Model):
id = db.Column(db.Integer,
primary_key=True,
nullable=False,
server_default=func.nextval())
username = db.Column(db.String(50), nullable=False)
password = db.Column(db.String(20), nullable=False)
def __init__(self, username, password):
self.username = username
self.password = password
def __repr__(self):
return '<UploadAcct %r>' % self.username
class Asset(db.Model):
id = db.Column(db.Integer,
primary_key=True,
nullable=False,
server_default=func.nextval())
name = db.Column(db.String(60), nullable=False)
location = db.Column(db.String(50), nullable=False)
ip_address = db.Column(db.String(50), nullable=False)
def __init__(self, name, location, ip_address):
self.name = name
self.location = location
self.ip_address = ip_address
def __repr__(self):
return '<Asset %r>' % self.name
def create_user(self, spec_data=None, return_object=True):
"""
Make a customer object, return the actual object with spec_data overriding values for further manipulation unless set to false.
:param spec_data: A dictionary containing the data keyed on db model object attribute
:param return_object: Whether to return the object or not, defaulting to True
:return: a customer db model
"""
u = User()
if spec_data is None:
spec_data = {}
sqlobj_from_dict(u, spec_data)
if u.id is None:
u.id = self.session.query(func.nextval('users_id_seq')).scalar()
if u.username is None:
u.username = '******' % u.id
if u.email is None:
u.email = '*****@*****.**' % u.id
if u.salt is None:
u.salt = 'generated_salt%d' % u.id
if u.password is None:
u.password = '******' % u.id
if isinstance(u.salt, basestring):
s = hashlib.sha512()
s.update(u.salt.encode('utf-8'))
u.salt = s.digest()
if isinstance(u.password, basestring):
m = hashlib.sha512()
m.update(u.password.encode('utf-8'))
m.update(u.salt)
u.password = m.digest()
self.session.add(u)
self.session.flush()
self.session.refresh(u)
if return_object:
return u
return u.id
class User(db.Model):
first = db.Column(db.String(30))
last = db.Column(db.String(30))
phone = db.Column(db.String(11))
email = db.Column(db.String(50), primary_key=True, nullable=False)
org = db.Column(db.String(50))
role = db.Column(db.String(10))
uploadid = db.Column(db.Integer)
password = db.Column(db.String(50))
id = db.Column(db.Integer, nullable=False, server_default=func.nextval())
def __init__(self, first, last, phone, email, org, role, uploadid,
password):
self.first = first
self.last = last
self.phone = phone
self.email = email
self.org = org
self.role = role
self.uploadid = uploadid
self.password = password
def __repr__(self):
return '<User %r %r>' % (self.first, self.last)
def nextObjectID(conn):
seq_sel = select([func.nextval('object_id_seq')])
# scalar executes, and gets first col of first row
result = conn.scalar(seq_sel)
return result
def nextObjectID(conn):
seq_sel = select([func.nextval('object_id_seq')])
# scalar executes, and gets first col of first row
result = conn.scalar(seq_sel)
return result
