[sqlalchemy] Mixing declarative style with expression language
Greetings, I trust everyone is doing well. Our code base uses SQLAlchemy and some of the old code uses expression language style code e.g. appts = Table(Appointment, META, autoload=True, autoload_with=DB) statement = select([appts.c.appointmentId], and_( appts.c.appointmentId == 212 )) results = select_all(statement) where as some of our code uses declarative style classes e.g. class Appointment(Alchemy_Base, QueryMixin): __tablename__ = Appointment appointmentId = Column(Integer, primary_key=True) @classmethod def get_by_id(cls, appointment_id): query = cls.query.filter_by(appointmentId=appointment_id) return query.one() Some of our scripts are going to use both of these files (both expression style and declarative style) so my question is, is it (i.e. mixing two styles of code) going to cause any sort of problems or we're going to be okay? I am asking because some people in our company are suggesting that we convert all code into one format (devs want to convert expression style old code into declarative style code). Kindly let me know your suggestions. BTW, we're using MySQL as database and Python 2.6.4. Thanks in advance. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] How to make sure all connections are closed?
Hi all, I've a Django app, structure of which looks something like http://codepad.org/Tha7ySNL . This app is serving few hundred customers and lately network admin said he's been seeing lot of open connections. What can I do to make sure there are no open connections? Is there anything that can guarantee there are no more open connections? All help will be really appreciated. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/PflV-kxCJCcJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Please help in completely converting raw sql to SQLAlchemy code(I tried but not there yet)
Greetings, I hope all are well. First off, all I'm sorry that my SQL
isn't great.
I've a raw SQL that looks like following
select a.appointmentId, a.patientId, r.MaxTime from (
select appointmentid, patientid, max(apptDate) as MaxTime
from appointment
where facilityid=95
group by patientid
) r
inner join
Appointment a on
a.patientid = r.patientid and
a.apptDate = r.MaxTime
I'm using declarative style of SQLAlchemy in my code and here is how
my query looks like
appt_query = alchemy_session.query(Appointment.appointmentId,
Appointment.patientId,
func.max(Appointment.apptDate).label('maxTime')).filter(
Appointment.facilityId == 95,
).group_by(Appointment.patientId).subquery()
appointments = alchemy_session.query(Appointment.appointmentId,
Appointment.patientId, appt_query.c.maxTime).outerjoin(
appt_query, and_(
Appointment.patientId == appt_query.c.patientId,
Appointment.apptDate == appt_query.c.maxTime
)
)
but when I do
print appointments
It's unfortunately not producing the SQL that I want. I know there are
lapses in my understanding of SQL so any pointers on this will be
really helpful. Thank you for your time and help.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How to fix mapped entity is required error?
Greetings, I'm using SQLAlchemy 0.6 with Python 2.6
medical-app/
facility/
recalls.py
common/
__init__.py
patient.py
common.py
settings.py
So the application I'm working on has directory structure as above.
Program starts when
I run recalls.py (which is under /medical-app/facility). Also,
recalls.py imports common.py
(which is under medical-app/common/common.py). Common.py further
imports settings.py which
has code like following
USERNAME = 'root'
PASSWORD = ''
SERVER = 'localhost'
DB_NAME = localdb'
DB = create_engine('mysql://'+USERNAME+':'+PASSWORD+'@'+SERVER
+'/'+DB_NAME, pool_size = 20, pool_recycle=100)
db_session = session.sessionmaker(bind=DB, expire_on_commit=False)
alchemy_session = db_session()
META = MetaData(DB)
In patient.py, I've declarative style classes as below
from common.settings import alchemy_session, DB, META
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table
Base = declarative_base()
class Patient(Base):
__table__ = Table('Patient', Base.metadata,
autoload=True, autoload_with=DB)
Then finally in recalls I do the following
from common import alchemy_session
from common.patient import Patient
patient = alchemy_session.query(Patient).filter(Patient.patientId ==
1).first()#this works
patient = alchemy_session.query(Patient).filter(Patient.patientId ==
2).first()#this fails
#with following error
patient = alchemy_session.query(Patient).filter(
File c:\python26\lib\site-packages\sqlalchemy-0.7.1-py2.6.egg
\sqlalchemy\orm\
session.py, line 897, in query
return self._query_cls(entities, self, **kwargs)
File c:\python26\lib\site-packages\sqlalchemy-0.7.1-py2.6.egg
\sqlalchemy\orm\
query.py, line 105, in __init__
self._set_entities(entities)
File c:\python26\lib\site-packages\sqlalchemy-0.7.1-py2.6.egg
\sqlalchemy\orm\
query.py, line 112, in _set_entities
entity_wrapper(self, ent)
File c:\python26\lib\site-packages\sqlalchemy-0.7.1-py2.6.egg
\sqlalchemy\orm\
query.py, line 2800, in __init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity exp
ected - got 'class 'vi_pycommon.patient.Patient''
D:\integration-scripts
\allendale_bone_jointalchemy_session.query(Patient).filte
r(Patient.patientId == 2).first()
---
so if you notice this line didn't fail
patient = alchemy_session.query(Patient).filter(Patient.patientId ==
1).first()
but next line did, which was
patient = alchemy_session.query(Patient).filter(Patient.patientId ==
2).first()#this fails
So I'm actually really stumped as to why this second call is failing
with that error. I am absolutely
sure I'm doing something wrong here, so kindly let me know how can I
fix this one.
Thanks in advance for all help.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] (Newbie)About loading a table only once
Greetings,
First, many thanks for creating such an awesome library. Secondly, I'm
very new to this and I've a basic question. I've a method that looks
like following
def test_table_load(self):
doctor = Table('TestTable', META, autoload=True, autoload_with=DB)
Now while the software runs, this method is called probably 40--50
times. I'm using MySQL and have following questions
- So if I call test_table_load() 10 times, SQLAlchemy will load the
database table with all records 10 times?
- If I want to call test_table_load() 10 times but I want to make sure
SQLAlchemy loads 'TestTable' only once in memory should I replace this
doctor = Table('Doctor', META, autoload=True, autoload_with=DB)
with
doctor = Table('Doctor', META, autoload=True, autoload_with=DB,
keep_existing=True)
If this is not the way to do it then please let me know how can I make
sure SQLAlchemy loads my table only once during the complete run of
the program?
Please enlighten me. I will really appreciate all the help.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] (Newbie)About loading a table only once
Greetings,
First, many thanks for creating such an awesome library. Secondly, I'm
very new to this and I've a basic question. I've a method that looks
like following
def test_table_load(self):
doctor = Table('TestTable', META, autoload=True, autoload_with=DB)
Now while the software runs, this method is called probably 40--50
times. I'm using MySQL and have following questions
- So if I call test_table_load() 10 times, SQLAlchemy will load the
database table with all records 10 times?
- If I want to call test_table_load() 10 times but I want to make sure
SQLAlchemy loads 'TestTable' only once in memory should I replace this
doctor = Table('Doctor', META, autoload=True, autoload_with=DB)
with
doctor = Table('Doctor', META, autoload=True, autoload_with=DB,
keep_existing=True)
If this is not the way to do it then please let me know how can I make
sure SQLAlchemy loads my table only once during the complete run of
the program?
Please enlighten me. I will really appreciate all the help.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
