[sqlalchemy] Updating children of a one-to-many bi-directional relationship
I had asked this question on Stack Overflow. The details are at this link: http://stackoverflow.com/questions/24836816/updating-a-few-children-in-one-to-many-relationship-deletes-all-rows-and-adds-ne The summary is I have a parent class A and a bidirectional one-to-many relationship with class B. When I update the class B list for an instance of A, the update may involve deleting some class B instances, updating some of them, and adding new ones. However, I find that SqlAlchemy deletes all ROWS of classB, and the inserts the necessary new rows. I would have expected SqlAchemy to insert only new rows, not delete and add those rows that are being updated. Do you know what is wrong with my code? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
RE: [sqlalchemy] change Oracle sequence on insert
Thanks Michael,
You of course is right and I rarely have to use this method.
Having said that, once in a while, if I need to migrate a version or do some
maintenance, I do need that option.
How would I do that in sqlalchemy? Do I have to use raw SQL for that? Why
would that not scale?
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Saturday, July 19, 2014 6:12 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] change Oracle sequence on insert
On Jul 19, 2014, at 3:38 AM, Ofir Herzas herz...@gmail.com wrote:
I have a table with the following column:
id = sa.Column(sa.Integer, sa.Sequence('id_seq'), primary_key=True,
nullable=False)
Usually, I have no problems inserting data, but every time I insert rows
with specific id, it causes problems with Oracle since the sequence is not
modified accordingly.
For example, assuming that the table is new and the sequence starts at 1, if
I insert a row specifying id=2, the sequence doesn't change which will cause
the next insert to fail.
I do understand that Oracle does not support auto increment but what is the
proper way of handling this under sqlalchemy? Do I need to manually change
the sequence after such insert statement? can I bind to an event or use any
other magic to make it work like other dialects? (choose max(id)+1)
the case where you have a sequence used for a table and at the same time you
have the need to insert rows with specific identifiers as a normal matter of
course (as opposed to when you need to do a bulk insert as part of database
maintenance) is an unusual one. Most database folks would ask why that's
the use case you have.Surrogate primary keys are not supposed to be
meaningful, you normally would just let the sequence handle creation of new
values 100% of the time.Because they increment atomically, you never
have to worry about two primary key identifiers conflicting. If you're
working around that then you can't be assured of integrity violations within
concurrent scenarios.
Short answer yes if you are inserting values directly then you need to
update the sequence manually, on oracle i think it might be ALTER SEQUENCE
or something like that. It's not the kind of thing that would scale,
though.
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/1hjzce5kg3Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Use relationship,can't do session.add (flask)
On Mon, Jul 21, 2014 at 2:40 AM, 'Frank Liou' via sqlalchemy sqlalchemy@googlegroups.com wrote: there is no error msg or how can i trace the error msg? OK, sorry, I misunderstood. How do you know it isn't working? Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Use relationship,can't do session.add (flask)
i use try
if do not return Success
it mean have not session.add
@app.route('/company/business_account_number/address/company_status/company_captial_amount/business_description/company_name',
methods=['POST'])
def
new_company_generation(business_account_number,address,company_status,company_captial_amount,business_description,company_name):
try:
company = Company()
company.create_new_company(business_account_number,address,company_status,company_captial_amount,business_description,company_name)
return Success!
except Exception:
return Failed!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] change Oracle sequence on insert
raw SQL and it wouldn't scale because you probably cannot emit two such ALTER
statements concurrently, and it probably doesn't run very fast either.
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2012.htm
On Jul 21, 2014, at 3:19 AM, Ofir Herzas herz...@gmail.com wrote:
Thanks Michael,
You of course is right and I rarely have to use this method.
Having said that, once in a while, if I need to migrate a version or do some
maintenance, I do need that option.
How would I do that in sqlalchemy? Do I have to use raw SQL for that? Why
would that not scale?
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Saturday, July 19, 2014 6:12 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] change Oracle sequence on insert
On Jul 19, 2014, at 3:38 AM, Ofir Herzas herz...@gmail.com wrote:
I have a table with the following column:
id = sa.Column(sa.Integer, sa.Sequence('id_seq'), primary_key=True,
nullable=False)
Usually, I have no problems inserting data, but every time I insert rows with
specific id, it causes problems with Oracle since the sequence is not
modified accordingly.
For example, assuming that the table is new and the sequence starts at 1, if
I insert a row specifying id=2, the sequence doesn't change which will cause
the next insert to fail.
I do understand that Oracle does not support auto increment but what is the
proper way of handling this under sqlalchemy? Do I need to manually change
the sequence after such insert statement? can I bind to an event or use any
other magic to make it work like other dialects? (choose max(id)+1)
the case where you have a sequence used for a table and at the same time you
have the need to insert rows with specific identifiers as a normal matter of
course (as opposed to when you need to do a bulk insert as part of database
maintenance) is an unusual one. Most database folks would ask why that's
the use case you have.Surrogate primary keys are not supposed to be
meaningful, you normally would just let the sequence handle creation of new
values 100% of the time.Because they increment atomically, you never have
to worry about two primary key identifiers conflicting. If you're working
around that then you can't be assured of integrity violations within
concurrent scenarios.
Short answer yes if you are inserting values directly then you need to update
the sequence manually, on oracle i think it might be ALTER SEQUENCE or
something like that. It's not the kind of thing that would scale, though.
--
You received this message because you are subscribed to a topic in the Google
Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/1hjzce5kg3Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email tosqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Updating children of a one-to-many bi-directional relationship
the code there doesn't seem to show anything that would result in any DELETEs emitted. a DELETE here would only occur if you deassociated a TestDevice and a TestPartition by removing from the TestDevice.partitions collection or setting a TestPartition.device to None, and I don't see that. All of the manipulations you're doing with part.partition_id have nothing to do with any of that, SQLAlchemy's relationships have no idea what you're doing with those, and overall it's a bad idea to mix the usage of obj.foreign_key = id along with direct manipulation of the relationship (where you say dev_part.device = ndev) together. SQLAlchemy's relationship management code knows nothing about any of those foreign key sets. See http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7. On Jul 21, 2014, at 2:55 AM, Bala Ramakrishnan bal...@gmail.com wrote: I had asked this question on Stack Overflow. The details are at this link: http://stackoverflow.com/questions/24836816/updating-a-few-children-in-one-to-many-relationship-deletes-all-rows-and-adds-ne The summary is I have a parent class A and a bidirectional one-to-many relationship with class B. When I update the class B list for an instance of A, the update may involve deleting some class B instances, updating some of them, and adding new ones. However, I find that SqlAlchemy deletes all ROWS of classB, and the inserts the necessary new rows. I would have expected SqlAchemy to insert only new rows, not delete and add those rows that are being updated. Do you know what is wrong with my code? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Updating children of a one-to-many bi-directional relationship
not to mention you're using strings to set integer values, again a bad idea, can only confuse your database: part.partition_id = '345' # -- this is a string partition_id = sa.Column(sa.Integer, nullable=False) # -- should be integer with this program you need to create a short test case that actually runs and step through it with pdb as well as echo=True to analyze more accurately what's happening and when. On Jul 21, 2014, at 10:10 AM, Michael Bayer mike...@zzzcomputing.com wrote: the code there doesn't seem to show anything that would result in any DELETEs emitted. a DELETE here would only occur if you deassociated a TestDevice and a TestPartition by removing from the TestDevice.partitions collection or setting a TestPartition.device to None, and I don't see that. All of the manipulations you're doing with part.partition_id have nothing to do with any of that, SQLAlchemy's relationships have no idea what you're doing with those, and overall it's a bad idea to mix the usage of obj.foreign_key = id along with direct manipulation of the relationship (where you say dev_part.device = ndev) together. SQLAlchemy's relationship management code knows nothing about any of those foreign key sets. See http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7. On Jul 21, 2014, at 2:55 AM, Bala Ramakrishnan bal...@gmail.com wrote: I had asked this question on Stack Overflow. The details are at this link: http://stackoverflow.com/questions/24836816/updating-a-few-children-in-one-to-many-relationship-deletes-all-rows-and-adds-ne The summary is I have a parent class A and a bidirectional one-to-many relationship with class B. When I update the class B list for an instance of A, the update may involve deleting some class B instances, updating some of them, and adding new ones. However, I find that SqlAlchemy deletes all ROWS of classB, and the inserts the necessary new rows. I would have expected SqlAchemy to insert only new rows, not delete and add those rows that are being updated. Do you know what is wrong with my code? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Use relationship,can't do session.add (flask)
You are deliberately suppressing the error message with your exception
handler in the new_company_generation function. Try getting rid of the
try: and exception Exception: lines, then you should see the full
error message, either in your web browser, or on the console where you
are running the flask application.
Simon
On Mon, Jul 21, 2014 at 10:57 AM, 'Frank Liou' via sqlalchemy
sqlalchemy@googlegroups.com wrote:
i use try
if do not return Success
it mean have not session.add
@app.route('/company/business_account_number/address/company_status/company_captial_amount/business_description/company_name',
methods=['POST'])
def
new_company_generation(business_account_number,address,company_status,company_captial_amount,business_description,company_name):
try:
company = Company()
company.create_new_company(business_account_number,address,company_status,company_captial_amount,business_description,company_name)
return Success!
except Exception:
return Failed!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
I agree with what Mike said, but I would just suggest renaming projector_table to something like purchased_table or inventory_table. Everything in models is a different model of a projector, so the table names are a bit confusing. In non-database terms, a good way to visualize relationships is to think of a form on a webpage. If you expect to select items from a dropdown select menu, then those things are usually best as something your foreign-key into their own table (or perhaps some sort of enumerated value, but that's another topic). If something is a bit of freeform text you enter, then it belongs in a column. You can also think of ways you'd want to search or display data. You might want to show projectors that only match a specific MFG, or have a specific type of source. Both of those requirements suggest having a seperate table with a foreign key might be a good idea ( vs a fulltext search ) That said, I'm unclear about what sources are. If a source is an AV input, then the source types would be global and shared. So a more normalized DB would have the sources in: sources_table = Table(u'source', metadata, Column(u'id', Integer, primary_key=True), Column(u'label', String(20)), ) model_2_source_table = Table(u'model_2_source', metadata, Column(u'model_id', Integer, primary_key=True, ForeignKey(u'model.id'), Column(u'source_id', Integer, primary_key=True, ForeignKey(u'source.id' ), ) If a source is a local input in your building though, then you would want to associate the 'source' with the inventory item you are tracking. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] [SQLAlchemy.Flask][2.x] OperationError unable to open databaes
Hello guys,
I am learning Flask, I new started sqlalchemy with flask, so very easy is
both.
I am working with wtforms flask extension and sqlalchemy for flask
extension.
My Application and database initalize,
app = Flask(__name__)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite::memory:'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:test.db'
db = SQLAlchemy(app)
And my sqlalchemy User table,
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
mail = db.Column(db.String(120), unique=True)
psw = db.Column(db.String(26), unique=True)
def __init__(self,mail,psw):
self.mail = mail
self.psw = psw
def __repr__(self):
return 'User %r - %r'%(self.mail, self.psw)
After I am calling *db.create_all()* func.
For example my simple register page,
@app.route('/reg')
def register():
formUser = userReg(request.form)
if request.method == POST and formUser.validate():
session['userEnabled'] = 1 #php style :)//
session['mail'] = formUser.mail.data
session['psw'] = formUser.psw.data
uyeObj = Kullanici(%s%formUser.mail.data,%s%formUser.psw.
data)
db.session.add(uyeObj)
db.session.commit()
return redirect('/profile')
return render_template('register.html', form=formUser)
I must this code checking, and as test, I am calling this function
*User.query.all()*
Here is error result
Traceback (most recent call last):
File C:\Users\user\Desktop\Flask\test\test.py, line 130, in module
xx = User.query.all()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py
, line 2293, in all
return list(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py
, line 2405, in __iter__
return self._execute_and_instances(context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py
, line 2418, in _execute_and_instances
close_with_result=True)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py
, line 2409, in _connection_from_session
**kw)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\session.py
, line 846, in connection
close_with_result=close_with_result)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\session.py
, line 850, in _connection_for_bind
return self.transaction._connection_for_bind(engine)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\session.py
, line 315, in _connection_for_bind
conn = bind.contextual_connect()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\base.py
, line 1737, in contextual_connect
self.pool.connect(),
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py
, line 332, in connect
return _ConnectionFairy._checkout(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py
, line 630, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py
, line 433, in checkout
rec = pool._do_get()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py
, line 1042, in _do_get
return self._create_connection()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py
, line 278, in _create_connection
return _ConnectionRecord(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py
, line 404, in __init__
self.connection = self.__connect()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py
, line 530, in __connect
connection = self.__pool._creator()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\strategies.py
, line 95, in connect
connection_invalidated=invalidated
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\util\compat.py
, line 189, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\strategies.py
, line 89, in connect
return dialect.connect(*cargs, **cparams)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\default.py
, line 376, in connect
return self.dbapi.connect(*cargs, **cparams)
OperationalError: (OperationalError) unable to open database file None None
How we do solve? I am trying two days. Today second day.
Thank you for interest.
Good works my friends.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
Re: [sqlalchemy] [SQLAlchemy.Flask][2.x] OperationError unable to open databaes
how are the permissions on this file path:
/test.db
?
do you really want your database file in the root directory like that?
On Jul 21, 2014, at 2:58 PM, Imk Hacked ihacked1...@gmail.com wrote:
Hello guys,
I am learning Flask, I new started sqlalchemy with flask, so very easy is
both.
I am working with wtforms flask extension and sqlalchemy for flask extension.
My Application and database initalize,
app = Flask(__name__)
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite::memory:'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:test.db'
db = SQLAlchemy(app)
And my sqlalchemy User table,
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
mail = db.Column(db.String(120), unique=True)
psw = db.Column(db.String(26), unique=True)
def __init__(self,mail,psw):
self.mail = mail
self.psw = psw
def __repr__(self):
return 'User %r - %r'%(self.mail, self.psw)
After I am calling db.create_all() func.
For example my simple register page,
@app.route('/reg')
def register():
formUser = userReg(request.form)
if request.method == POST and formUser.validate():
session['userEnabled'] = 1 #php style :)//
session['mail'] = formUser.mail.data
session['psw'] = formUser.psw.data
uyeObj = Kullanici(%s%formUser.mail.data,%s%formUser.psw.data)
db.session.add(uyeObj)
db.session.commit()
return redirect('/profile')
return render_template('register.html', form=formUser)
I must this code checking, and as test, I am calling this function
User.query.all()
Here is error result
Traceback (most recent call last):
File C:\Users\user\Desktop\Flask\test\test.py, line 130, in module
xx = User.query.all()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py,
line 2293, in all
return list(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py,
line 2405, in __iter__
return self._execute_and_instances(context)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py,
line 2418, in _execute_and_instances
close_with_result=True)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\query.py,
line 2409, in _connection_from_session
**kw)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\session.py,
line 846, in connection
close_with_result=close_with_result)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\session.py,
line 850, in _connection_for_bind
return self.transaction._connection_for_bind(engine)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\orm\session.py,
line 315, in _connection_for_bind
conn = bind.contextual_connect()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\base.py,
line 1737, in contextual_connect
self.pool.connect(),
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py,
line 332, in connect
return _ConnectionFairy._checkout(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py,
line 630, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py,
line 433, in checkout
rec = pool._do_get()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py,
line 1042, in _do_get
return self._create_connection()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py,
line 278, in _create_connection
return _ConnectionRecord(self)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py,
line 404, in __init__
self.connection = self.__connect()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\pool.py,
line 530, in __connect
connection = self.__pool._creator()
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\strategies.py,
line 95, in connect
connection_invalidated=invalidated
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\util\compat.py,
line 189, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\strategies.py,
line 89, in connect
return dialect.connect(*cargs, **cparams)
File
C:\Python27\lib\site-packages\sqlalchemy-0.9.6-py2.7-win32.egg\sqlalchemy\engine\default.py,
line 376, in connect
return self.dbapi.connect(*cargs, **cparams)
OperationalError:
Re: [sqlalchemy] Multiple tables and foreignkey constraints
On Monday, July 21, 2014 8:38:54 AM UTC-7, Jonathan Vanasco wrote: I agree with what Mike said, but I would just suggest renaming projector_table to something like purchased_table or inventory_table. Everything in models is a different model of a projector, so the table names are a bit confusing. snip Short answer: manufacturer/model tables are projectors, and sources are the video inputs available for that particular model. The sources table is going to be used to keep track of what the manufacturer has listed in their documentation for selecting a particular video source. The projectors_table is persistent storage use for what projectors that the user can control via networking. Basic information is some text columns that the end-user can assign for their own (short) notes, but the model_id field is used so that the text can be matched with the projector that they have control over. Longer answer. Column 1 is PJLink code for selecting that input, the rest of the line is text. Example for Eiki model LC/XL200 projector: (Text is from Eiki webpage control) 11 RGB (pc analog) 12 RGB (Scart) 13 RGB (PC Digital) Example Hitachi CP-X2514: (Text is from PJLink user manual from Hitachi) 11 Computer IN 1 12 Computer IN 2 13 Component As noted, different manufacturers may have different text for the same inputs, so the sources table is just keeping track of the text for the input source - hopefully text that the end-user does not find too confusing :) This is not an inventory program. The part I'm looking to add is basic projector control to a program that will send some output via a second computer video output to a projector. One point is that there may be multiple computers connected to a single projector via multiple inputs (sources). I was thinking of having manufacturer/model/source tables so the end-user doesn't have to re-enter the information if they just happen to have multiple projectors with the same model - as a side possibility, also having an xml file with this information available that can be imported into those tables. When the end-user adds a projector to the program, they can select the projector by manufacturer (Eiki) - model (LC/XL200) - then the sources (video inputs) would be added to the projector class so they can then select the video source to display. Since using PJLink codes would be confusing (What does input 11 select?), the text pulled from the sources table would then let them use the familiar text (documented in their projector user manual - like RGB (pc analog) ) to select the source. An example xml file for importing would look something like: projector manufacturer='Eiki' model name='LC/XL200' source pjlink='11'RGB (PC analog)/source source pjlink='12'RGB (Scart)/source source pjlink='13'RGB (PC digital)/source /model model name=.'...' /model /projector With the importing in mind, there still has to be the option for the end-user to manually add an entry for projector manufacturer/model/sources (technical note, with PJLink, I can retrieve the manufacturer name, model name, and the available sources via the network, just not the text for the sources). With that, then Jonathan's suggestion of removing the foreign_key on the sources table and create a 4th table that keeps track of the model-sources constraints. As for the projectors_table, instead of a foreign_key just use an integer column as an index into the models_table would be the suggestion? projector_table = Table(u'projector', metadata, Column(u'id', Integer, primary_key=True), Column(u'model_2_source_id', Integer) ) The way things are looking, looks like I'm going to have multiple selects. Not an issue, since they will only be used on program startup, not during normal operations. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
Forgot to mention that during typical operation, the only time the database will be accessed would be during down time (add/delete) or program startup (retrieve list of projectors to control) - not during a presentation. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
