[sqlalchemy] Re: dealing with NULLS in 1-many relationships
I would ultimately like to do something like:
provider_id = Column(Integer,
func.coalesce(ForeignKey('cp_provider.provider_id'), 'Missing'),
nullable=True)
but this is not working... I also tried using coalesce with a primaryjoin
condition on
encounter = relationship("EncounterList", backref=backref("Provider"),
lazy='dynamic')
but neither did this.
On Monday, June 6, 2016 at 10:46:23 AM UTC-5, Horcle wrote:
>
> So I can set it to a default value, such as 'N/A' - something similar to
> the MSSQL function ISNULL that I can use in the class definition.
>
> On Monday, June 6, 2016 at 10:41:08 AM UTC-5, Horcle wrote:
>>
>> I'm basically looking for something I can add to the backref or
>> ForeignKey definition for the case that the value of provider_id is None.
>>
>> On Monday, June 6, 2016 at 10:21:39 AM UTC-5, Horcle wrote:
>>>
>>> I have the following models:
>>>
>>> class LabResult(Model):
>>> __tablename__ = 'cp_svc_lab_result'
>>> id = Column(Integer, primary_key=True, autoincrement=True)
>>> test_code = Column(String(255))
>>> test_code_system = Column(String(255))
>>> test_name = Column(String(255))
>>> test_name_orig = Column(String(255))
>>> proc_name = Column(String(255))
>>> proc_code = Column(String(255))
>>> proc_code_modifier = Column(String(255))
>>> proc_code_system = Column(String(255))
>>> result_value = Column(String(255))
>>> result_value_num = Column(String(255))
>>> result_value_num_orig = Column(String(255))
>>> result_unit = Column(String(255))
>>> result_unit_orig = Column(String(255))
>>> ref_normal_min = Column(String(255))
>>> ref_normal_max = Column(String(255))
>>> result_characterization = Column(String(255))
>>> collection_datetime = Column(DateTime)
>>> result_datetime = Column(DateTime)
>>> abnormal_flag = Column(String(255))
>>> lab_status = Column(String(255))
>>> result_comment = Column(UnicodeText)
>>> component_comment = Column(UnicodeText)
>>> order_id = Column(String(255))
>>> order_num = Column(String(255))
>>> order_priority = Column(String(255))
>>> order_result_id = Column(String(255))
>>> order_reviewed = Column(String(255))
>>> order_type_orig = Column(String(255))
>>> order_type_orig_id = Column(String(255))
>>> result_code_orig = Column(String(255))
>>> result_code_orig_system = Column(String(255))
>>> result_status = Column(String(255))
>>> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
>>> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
>>> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>>>
>>> and,
>>>
>>> class Provider(Model):
>>> __tablename__ = 'cp_provider'
>>> provider_id = Column(Integer, primary_key=True)
>>> authorize_meds_yn = Column(String(80))
>>> active_status = Column(String(80))
>>> authorize_orders_yn = Column(String(80))
>>> birth_date = Column(DateTime)
>>> clinician_degree = Column(String(80))
>>> clinician_title = Column(String(80))
>>> country = Column(String(80))
>>> dea_number = Column(String(80))
>>> email = Column(String(80))
>>> external_name = Column(String(80))
>>> provider_e_prescribe_yn = Column(String(80))
>>> inpatient_ordering_yn = Column(String(80))
>>> name = Column(String(80))
>>> npi = Column(String(80))
>>> office_fax = Column(String(80))
>>> office_phone = Column(String(80))
>>> outpatient_ordering_yn = Column(String(80))
>>> provider_type = Column(String(80))
>>> referral_source_type = Column(String(80))
>>> resident_yn = Column(String(80))
>>> sex = Column(String(80))
>>> surgical_pool_yn = Column(String(80))
>>> transcription_user_yn = Column(String(80))
>>> upin = Column(String(80))
>>> encounter = relationship("EncounterList", backref=backref("Provider"),
>>> lazy='dynamic')
>>>
>>> Where one provider can have multiple LabResults... How do I handle the
>>> case when there may be a provider_id in the LabResult table, but not in the
>>> Provider table (we are only keeping a subset of the provider list)? I need
>>> to access the object Provider so that I can have access to all of its
>>> attributes, such as Provider.name, etc. When I try this now, I get an error
>>> that "Nonetype has attribute name." Ia there a way to set a default value
>>> for when the result is NULL?
>>>
>>> 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: dealing with NULLS in 1-many relationships
So I can set it to a default value, such as 'N/A' - something similar to
the MSSQL function ISNULL that I can use in the class definition.
On Monday, June 6, 2016 at 10:41:08 AM UTC-5, Horcle wrote:
>
> I'm basically looking for something I can add to the backref or ForeignKey
> definition for the case that the value of provider_id is None.
>
> On Monday, June 6, 2016 at 10:21:39 AM UTC-5, Horcle wrote:
>>
>> I have the following models:
>>
>> class LabResult(Model):
>> __tablename__ = 'cp_svc_lab_result'
>> id = Column(Integer, primary_key=True, autoincrement=True)
>> test_code = Column(String(255))
>> test_code_system = Column(String(255))
>> test_name = Column(String(255))
>> test_name_orig = Column(String(255))
>> proc_name = Column(String(255))
>> proc_code = Column(String(255))
>> proc_code_modifier = Column(String(255))
>> proc_code_system = Column(String(255))
>> result_value = Column(String(255))
>> result_value_num = Column(String(255))
>> result_value_num_orig = Column(String(255))
>> result_unit = Column(String(255))
>> result_unit_orig = Column(String(255))
>> ref_normal_min = Column(String(255))
>> ref_normal_max = Column(String(255))
>> result_characterization = Column(String(255))
>> collection_datetime = Column(DateTime)
>> result_datetime = Column(DateTime)
>> abnormal_flag = Column(String(255))
>> lab_status = Column(String(255))
>> result_comment = Column(UnicodeText)
>> component_comment = Column(UnicodeText)
>> order_id = Column(String(255))
>> order_num = Column(String(255))
>> order_priority = Column(String(255))
>> order_result_id = Column(String(255))
>> order_reviewed = Column(String(255))
>> order_type_orig = Column(String(255))
>> order_type_orig_id = Column(String(255))
>> result_code_orig = Column(String(255))
>> result_code_orig_system = Column(String(255))
>> result_status = Column(String(255))
>> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
>> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
>> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>>
>> and,
>>
>> class Provider(Model):
>> __tablename__ = 'cp_provider'
>> provider_id = Column(Integer, primary_key=True)
>> authorize_meds_yn = Column(String(80))
>> active_status = Column(String(80))
>> authorize_orders_yn = Column(String(80))
>> birth_date = Column(DateTime)
>> clinician_degree = Column(String(80))
>> clinician_title = Column(String(80))
>> country = Column(String(80))
>> dea_number = Column(String(80))
>> email = Column(String(80))
>> external_name = Column(String(80))
>> provider_e_prescribe_yn = Column(String(80))
>> inpatient_ordering_yn = Column(String(80))
>> name = Column(String(80))
>> npi = Column(String(80))
>> office_fax = Column(String(80))
>> office_phone = Column(String(80))
>> outpatient_ordering_yn = Column(String(80))
>> provider_type = Column(String(80))
>> referral_source_type = Column(String(80))
>> resident_yn = Column(String(80))
>> sex = Column(String(80))
>> surgical_pool_yn = Column(String(80))
>> transcription_user_yn = Column(String(80))
>> upin = Column(String(80))
>> encounter = relationship("EncounterList", backref=backref("Provider"),
>> lazy='dynamic')
>>
>> Where one provider can have multiple LabResults... How do I handle the
>> case when there may be a provider_id in the LabResult table, but not in the
>> Provider table (we are only keeping a subset of the provider list)? I need
>> to access the object Provider so that I can have access to all of its
>> attributes, such as Provider.name, etc. When I try this now, I get an error
>> that "Nonetype has attribute name." Ia there a way to set a default value
>> for when the result is NULL?
>>
>> 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: dealing with NULLS in 1-many relationships
I'm basically looking for something I can add to the backref or ForeignKey
definition for the case that the value of provider_id is None.
On Monday, June 6, 2016 at 10:21:39 AM UTC-5, Horcle wrote:
>
> I have the following models:
>
> class LabResult(Model):
> __tablename__ = 'cp_svc_lab_result'
> id = Column(Integer, primary_key=True, autoincrement=True)
> test_code = Column(String(255))
> test_code_system = Column(String(255))
> test_name = Column(String(255))
> test_name_orig = Column(String(255))
> proc_name = Column(String(255))
> proc_code = Column(String(255))
> proc_code_modifier = Column(String(255))
> proc_code_system = Column(String(255))
> result_value = Column(String(255))
> result_value_num = Column(String(255))
> result_value_num_orig = Column(String(255))
> result_unit = Column(String(255))
> result_unit_orig = Column(String(255))
> ref_normal_min = Column(String(255))
> ref_normal_max = Column(String(255))
> result_characterization = Column(String(255))
> collection_datetime = Column(DateTime)
> result_datetime = Column(DateTime)
> abnormal_flag = Column(String(255))
> lab_status = Column(String(255))
> result_comment = Column(UnicodeText)
> component_comment = Column(UnicodeText)
> order_id = Column(String(255))
> order_num = Column(String(255))
> order_priority = Column(String(255))
> order_result_id = Column(String(255))
> order_reviewed = Column(String(255))
> order_type_orig = Column(String(255))
> order_type_orig_id = Column(String(255))
> result_code_orig = Column(String(255))
> result_code_orig_system = Column(String(255))
> result_status = Column(String(255))
> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>
> and,
>
> class Provider(Model):
> __tablename__ = 'cp_provider'
> provider_id = Column(Integer, primary_key=True)
> authorize_meds_yn = Column(String(80))
> active_status = Column(String(80))
> authorize_orders_yn = Column(String(80))
> birth_date = Column(DateTime)
> clinician_degree = Column(String(80))
> clinician_title = Column(String(80))
> country = Column(String(80))
> dea_number = Column(String(80))
> email = Column(String(80))
> external_name = Column(String(80))
> provider_e_prescribe_yn = Column(String(80))
> inpatient_ordering_yn = Column(String(80))
> name = Column(String(80))
> npi = Column(String(80))
> office_fax = Column(String(80))
> office_phone = Column(String(80))
> outpatient_ordering_yn = Column(String(80))
> provider_type = Column(String(80))
> referral_source_type = Column(String(80))
> resident_yn = Column(String(80))
> sex = Column(String(80))
> surgical_pool_yn = Column(String(80))
> transcription_user_yn = Column(String(80))
> upin = Column(String(80))
> encounter = relationship("EncounterList", backref=backref("Provider"),
> lazy='dynamic')
>
> Where one provider can have multiple LabResults... How do I handle the
> case when there may be a provider_id in the LabResult table, but not in the
> Provider table (we are only keeping a subset of the provider list)? I need
> to access the object Provider so that I can have access to all of its
> attributes, such as Provider.name, etc. When I try this now, I get an error
> that "Nonetype has attribute name." Ia there a way to set a default value
> for when the result is NULL?
>
> 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Dealing with large collections in a scaffolded application
This worked, btw.
Thanks!
On Wednesday, June 1, 2016 at 5:45:45 PM UTC-5, Horcle wrote:
>
> Ha! Yes, I should not have taken this literally. Will try tomorrow and let
> you know the outcome.
>
> Thanks!
>
> On Wednesday, June 1, 2016 at 3:55:35 PM UTC-5, Mike Bayer wrote:
>>
>> there's no "eager" strategy. you'd want something like lazy="joined" or
>> something like that. check the docs.
>>
>>
>>
>> On 06/01/2016 04:19 PM, Horcle wrote:
>> > Hi Mike,
>> > Just verifying:
>> >
>> > Should
>> >
>> > class Patient(Model):
>> > encounters = relationship(
>> > "Encounter",
>> > backref_populates='patient',
>> > lazy='dynamic')
>> >
>> > be
>> >
>> > class Patient(Model):
>> > encounters = relationship(
>> > "Encounter",
>> > back_populates='patient',
>> > lazy='dynamic')
>> >
>> >
>> > If this is true, I am getting an error that
>> >
>> > Exception: can't locate strategy for > > 'sqlalchemy.orm.relationships.RelationshipProperty'> (('lazy',
>> 'eager'),)
>> >
>> > Btw, we rebuilt the database using patient_id as a primary key.
>> >
>> > Thanks for your help!
>> >
>> > Greg--
>> >
>> > On Tuesday, May 31, 2016 at 3:49:29 PM UTC-5, Mike Bayer wrote:
>> >
>> > you should use "dynamic" and your relationships are mis-configured
>> with
>> > mis-use of the backref() construct, as you've constructed
>> relationships
>> > on both sides that are mutual you'd use back_populates:
>> >
>> >
>> > class Encounter(Model):
>> > patient = relationship(
>> >"Patient",
>> >back_populates='encounters',
>> >lazy='eager')
>> >
>> > class Patent(Model):
>> > encounters = relationship(
>> > "Encounter",
>> > backref_populates='patient',
>> > lazy='dynamic')
>> >
>> >
>> > Also the foreign key from Encounter.patient_id to
>> Patient.patient_id
>> > does not illustrate a unique index in use and will not be accepted
>> by
>> > all database backends as a real constraint unless one is added (and
>> > regardless, performance will suffer without an index on this
>> column).
>> > Typically, Encounter.patient_id would refer to the primary key of
>> > Patient which is Patient.id.
>> >
>> >
>> >
>> > On 05/31/2016 04:38 PM, Horcle wrote:
>> > > I guess my question is: How can I efficiently load Patient and
>> its
>> > > related Encounters? I have tried various loading strategies of
>> > dynamic,
>> > > joined (I would think this would be the desired option),
>> subquery, no
>> > > load, etc., and it these do not load. On the other hand, I can
>> load
>> > > Encounter just fine and access an instance of the Patient object.
>> > >
>> > >
>> > >
>> > > On Tuesday, May 31, 2016 at 11:20:37 AM UTC-5, Horcle wrote:
>> > >
>> > > I have the following two models:
>> > >
>> > > |
>> > > ClassEncounter(Model):
>> > > __tablename__ ='cp_service'
>> > > id =Column(Integer,primary_key=True,autoincrement=True)
>> > > master_service_id =Column(String(255))
>> > > admission_datetime =Column(DateTime)
>> > > admission_provider_id =Column(String(255))
>> > > admission_status =Column(String(255))
>> > > age_at_visit =Column(String(255))
>> > > attending_provider_id =Column(String(255))
>> > > center =Column(String(255))
>> > > department_name =Column(String(255))
>> > > discharge_datetime =Column(DateTime)
>> > > encounter_category =Column(String(255))
>> > > encounter_class =Column(String(255))
>> > >
[sqlalchemy] dealing with NULLS in 1-many relationships
I have the following models:
class LabResult(Model):
__tablename__ = 'cp_svc_lab_result'
id = Column(Integer, primary_key=True, autoincrement=True)
test_code = Column(String(255))
test_code_system = Column(String(255))
test_name = Column(String(255))
test_name_orig = Column(String(255))
proc_name = Column(String(255))
proc_code = Column(String(255))
proc_code_modifier = Column(String(255))
proc_code_system = Column(String(255))
result_value = Column(String(255))
result_value_num = Column(String(255))
result_value_num_orig = Column(String(255))
result_unit = Column(String(255))
result_unit_orig = Column(String(255))
ref_normal_min = Column(String(255))
ref_normal_max = Column(String(255))
result_characterization = Column(String(255))
collection_datetime = Column(DateTime)
result_datetime = Column(DateTime)
abnormal_flag = Column(String(255))
lab_status = Column(String(255))
result_comment = Column(UnicodeText)
component_comment = Column(UnicodeText)
order_id = Column(String(255))
order_num = Column(String(255))
order_priority = Column(String(255))
order_result_id = Column(String(255))
order_reviewed = Column(String(255))
order_type_orig = Column(String(255))
order_type_orig_id = Column(String(255))
result_code_orig = Column(String(255))
result_code_orig_system = Column(String(255))
result_status = Column(String(255))
patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
service_id = Column(Integer, ForeignKey('cp_service.service_id'))
provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
and,
class Provider(Model):
__tablename__ = 'cp_provider'
provider_id = Column(Integer, primary_key=True)
authorize_meds_yn = Column(String(80))
active_status = Column(String(80))
authorize_orders_yn = Column(String(80))
birth_date = Column(DateTime)
clinician_degree = Column(String(80))
clinician_title = Column(String(80))
country = Column(String(80))
dea_number = Column(String(80))
email = Column(String(80))
external_name = Column(String(80))
provider_e_prescribe_yn = Column(String(80))
inpatient_ordering_yn = Column(String(80))
name = Column(String(80))
npi = Column(String(80))
office_fax = Column(String(80))
office_phone = Column(String(80))
outpatient_ordering_yn = Column(String(80))
provider_type = Column(String(80))
referral_source_type = Column(String(80))
resident_yn = Column(String(80))
sex = Column(String(80))
surgical_pool_yn = Column(String(80))
transcription_user_yn = Column(String(80))
upin = Column(String(80))
encounter = relationship("EncounterList", backref=backref("Provider"), lazy=
'dynamic')
Where one provider can have multiple LabResults... How do I handle the case
when there may be a provider_id in the LabResult table, but not in the
Provider table (we are only keeping a subset of the provider list)? I need
to access the object Provider so that I can have access to all of its
attributes, such as Provider.name, etc. When I try this now, I get an error
that "Nonetype has attribute name." Ia there a way to set a default value
for when the result is NULL?
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Dealing with large collections in a scaffolded application
Ha! Yes, I should not have taken this literally. Will try tomorrow and let
you know the outcome.
Thanks!
On Wednesday, June 1, 2016 at 3:55:35 PM UTC-5, Mike Bayer wrote:
>
> there's no "eager" strategy. you'd want something like lazy="joined" or
> something like that. check the docs.
>
>
>
> On 06/01/2016 04:19 PM, Horcle wrote:
> > Hi Mike,
> > Just verifying:
> >
> > Should
> >
> > class Patient(Model):
> > encounters = relationship(
> > "Encounter",
> > backref_populates='patient',
> > lazy='dynamic')
> >
> > be
> >
> > class Patient(Model):
> > encounters = relationship(
> > "Encounter",
> > back_populates='patient',
> > lazy='dynamic')
> >
> >
> > If this is true, I am getting an error that
> >
> > Exception: can't locate strategy for > 'sqlalchemy.orm.relationships.RelationshipProperty'> (('lazy',
> 'eager'),)
> >
> > Btw, we rebuilt the database using patient_id as a primary key.
> >
> > Thanks for your help!
> >
> > Greg--
> >
> > On Tuesday, May 31, 2016 at 3:49:29 PM UTC-5, Mike Bayer wrote:
> >
> > you should use "dynamic" and your relationships are mis-configured
> with
> > mis-use of the backref() construct, as you've constructed
> relationships
> > on both sides that are mutual you'd use back_populates:
> >
> >
> > class Encounter(Model):
> > patient = relationship(
> >"Patient",
> >back_populates='encounters',
> >lazy='eager')
> >
> > class Patent(Model):
> > encounters = relationship(
> > "Encounter",
> > backref_populates='patient',
> > lazy='dynamic')
> >
> >
> > Also the foreign key from Encounter.patient_id to Patient.patient_id
> > does not illustrate a unique index in use and will not be accepted
> by
> > all database backends as a real constraint unless one is added (and
> > regardless, performance will suffer without an index on this
> column).
> > Typically, Encounter.patient_id would refer to the primary key of
> > Patient which is Patient.id.
> >
> >
> >
> > On 05/31/2016 04:38 PM, Horcle wrote:
> > > I guess my question is: How can I efficiently load Patient and its
> > > related Encounters? I have tried various loading strategies of
> > dynamic,
> > > joined (I would think this would be the desired option), subquery,
> no
> > > load, etc., and it these do not load. On the other hand, I can
> load
> > > Encounter just fine and access an instance of the Patient object.
> > >
> > >
> > >
> > > On Tuesday, May 31, 2016 at 11:20:37 AM UTC-5, Horcle wrote:
> > >
> > > I have the following two models:
> > >
> > > |
> > > ClassEncounter(Model):
> > > __tablename__ ='cp_service'
> > > id =Column(Integer,primary_key=True,autoincrement=True)
> > > master_service_id =Column(String(255))
> > > admission_datetime =Column(DateTime)
> > > admission_provider_id =Column(String(255))
> > > admission_status =Column(String(255))
> > > age_at_visit =Column(String(255))
> > > attending_provider_id =Column(String(255))
> > > center =Column(String(255))
> > > department_name =Column(String(255))
> > > discharge_datetime =Column(DateTime)
> > > encounter_category =Column(String(255))
> > > encounter_class =Column(String(255))
> > > encounter_date =Column(DateTime)
> > > encounter_setting =Column(String(255))
> > > encounter_type =Column(String(255))
> > > primary_care_provider_id =Column(String(255))
> > > primary_service_yn =Column(String(255))
> > > provider_id =Column(String(255))
> > > serv_area_id_orig =Column(String(255))
> > > service_id =Column(Integer)
> > > patient_id
> > =Column
Re: [sqlalchemy] Re: Dealing with large collections in a scaffolded application
Hi Mike,
Just verifying:
Should
class Patient(Model):
encounters = relationship(
"Encounter",
backref_populates='patient',
lazy='dynamic')
be
class Patient(Model):
encounters = relationship(
"Encounter",
back_populates='patient',
lazy='dynamic')
If this is true, I am getting an error that
Exception: can't locate strategy for (('lazy', 'eager'),)
Btw, we rebuilt the database using patient_id as a primary key.
Thanks for your help!
Greg--
On Tuesday, May 31, 2016 at 3:49:29 PM UTC-5, Mike Bayer wrote:
>
> you should use "dynamic" and your relationships are mis-configured with
> mis-use of the backref() construct, as you've constructed relationships
> on both sides that are mutual you'd use back_populates:
>
>
> class Encounter(Model):
> patient = relationship(
>"Patient",
>back_populates='encounters',
>lazy='eager')
>
> class Patent(Model):
> encounters = relationship(
> "Encounter",
> backref_populates='patient',
> lazy='dynamic')
>
>
> Also the foreign key from Encounter.patient_id to Patient.patient_id
> does not illustrate a unique index in use and will not be accepted by
> all database backends as a real constraint unless one is added (and
> regardless, performance will suffer without an index on this column).
> Typically, Encounter.patient_id would refer to the primary key of
> Patient which is Patient.id.
>
>
>
> On 05/31/2016 04:38 PM, Horcle wrote:
> > I guess my question is: How can I efficiently load Patient and its
> > related Encounters? I have tried various loading strategies of dynamic,
> > joined (I would think this would be the desired option), subquery, no
> > load, etc., and it these do not load. On the other hand, I can load
> > Encounter just fine and access an instance of the Patient object.
> >
> >
> >
> > On Tuesday, May 31, 2016 at 11:20:37 AM UTC-5, Horcle wrote:
> >
> > I have the following two models:
> >
> > |
> > ClassEncounter(Model):
> > __tablename__ ='cp_service'
> > id =Column(Integer,primary_key=True,autoincrement=True)
> > master_service_id =Column(String(255))
> > admission_datetime =Column(DateTime)
> > admission_provider_id =Column(String(255))
> > admission_status =Column(String(255))
> > age_at_visit =Column(String(255))
> > attending_provider_id =Column(String(255))
> > center =Column(String(255))
> > department_name =Column(String(255))
> > discharge_datetime =Column(DateTime)
> > encounter_category =Column(String(255))
> > encounter_class =Column(String(255))
> > encounter_date =Column(DateTime)
> > encounter_setting =Column(String(255))
> > encounter_type =Column(String(255))
> > primary_care_provider_id =Column(String(255))
> > primary_service_yn =Column(String(255))
> > provider_id =Column(String(255))
> > serv_area_id_orig =Column(String(255))
> > service_id =Column(Integer)
> > patient_id =Column(Integer,ForeignKey('cp_patient.patient_id'))
> > patient
> >
> =relationship("Patient",backref=backref("patientEncounter"),lazy='eager',primaryjoin="Patient.patient_id==Encounter.patient_id")
>
>
> > |
> >
> > |
> > classPatient(Model):
> > __tablename__ ='cp_patient'
> > id =Column(Integer,primary_key=True,autoincrement=True)
> > first_name =Column(String(100))
> > middle_name =Column(String(80))
> > last_name =Column(String(80))
> > sex =Column(String(80))
> > race =Column(String(80))
> > birth_date =Column(DateTime)
> > ethnicity =Column(String(80))
> > race =Column(String(80))
> > patient_id =Column(Integer)
> > mrn =Column(String(80))
> > title =Column(String(80))
> > suffix =Column(String(80))
> > name_alias =Column(String(80))
> > addr_1 =Column(String(80))
> > addr_2 =Column(String(80))
> > addr_3 =Column(String(80))
> > city =Column(String(80))
> > state =Column(String(80))
> > country =Column(String(80))
> > zip =Column(String(80))
Re: [sqlalchemy] Re: Dealing with large collections in a scaffolded application
Hi Michael,
The backref/backpopulate was my attempt at cleaning this up, and I've
obviously made more of a mess of it. (^_^)
The patient_id as a FK (versus use of the PK from the patient table) was a
deliberate choice for ease of getting the db up. It was indexed in the
patient table on the db server, but not uniquely, so we are adding this
now. I assume it would behoove us to also add this as a unique index in the
Patient class? Looking through the documents, this appears to be defined as
a UniqueContraint. I assume to use this in the Patient class I would need
to define it as a __table_args__?
Thanks again!
Greg--
On Tuesday, May 31, 2016 at 3:49:29 PM UTC-5, Mike Bayer wrote:
>
> you should use "dynamic" and your relationships are mis-configured with
> mis-use of the backref() construct, as you've constructed relationships
> on both sides that are mutual you'd use back_populates:
>
>
> class Encounter(Model):
> patient = relationship(
>"Patient",
>back_populates='encounters',
>lazy='eager')
>
> class Patent(Model):
> encounters = relationship(
> "Encounter",
> backref_populates='patient',
> lazy='dynamic')
>
>
> Also the foreign key from Encounter.patient_id to Patient.patient_id
> does not illustrate a unique index in use and will not be accepted by
> all database backends as a real constraint unless one is added (and
> regardless, performance will suffer without an index on this column).
> Typically, Encounter.patient_id would refer to the primary key of
> Patient which is Patient.id.
>
>
>
> On 05/31/2016 04:38 PM, Horcle wrote:
> > I guess my question is: How can I efficiently load Patient and its
> > related Encounters? I have tried various loading strategies of dynamic,
> > joined (I would think this would be the desired option), subquery, no
> > load, etc., and it these do not load. On the other hand, I can load
> > Encounter just fine and access an instance of the Patient object.
> >
> >
> >
> > On Tuesday, May 31, 2016 at 11:20:37 AM UTC-5, Horcle wrote:
> >
> > I have the following two models:
> >
> > |
> > ClassEncounter(Model):
> > __tablename__ ='cp_service'
> > id =Column(Integer,primary_key=True,autoincrement=True)
> > master_service_id =Column(String(255))
> > admission_datetime =Column(DateTime)
> > admission_provider_id =Column(String(255))
> > admission_status =Column(String(255))
> > age_at_visit =Column(String(255))
> > attending_provider_id =Column(String(255))
> > center =Column(String(255))
> > department_name =Column(String(255))
> > discharge_datetime =Column(DateTime)
> > encounter_category =Column(String(255))
> > encounter_class =Column(String(255))
> > encounter_date =Column(DateTime)
> > encounter_setting =Column(String(255))
> > encounter_type =Column(String(255))
> > primary_care_provider_id =Column(String(255))
> > primary_service_yn =Column(String(255))
> > provider_id =Column(String(255))
> > serv_area_id_orig =Column(String(255))
> > service_id =Column(Integer)
> > patient_id =Column(Integer,ForeignKey('cp_patient.patient_id'))
> > patient
> >
> =relationship("Patient",backref=backref("patientEncounter"),lazy='eager',primaryjoin="Patient.patient_id==Encounter.patient_id")
>
>
> > |
> >
> > |
> > classPatient(Model):
> > __tablename__ ='cp_patient'
> > id =Column(Integer,primary_key=True,autoincrement=True)
> > first_name =Column(String(100))
> > middle_name =Column(String(80))
> > last_name =Column(String(80))
> > sex =Column(String(80))
> > race =Column(String(80))
> > birth_date =Column(DateTime)
> > ethnicity =Column(String(80))
> > race =Column(String(80))
> > patient_id =Column(Integer)
> > mrn =Column(String(80))
> > title =Column(String(80))
> > suffix =Column(String(80))
> > name_alias =Column(String(80))
> > addr_1 =Column(String(80))
> > addr_2 =Column(String(80))
> > addr_3 =Column(String(80))
> > city =Column(String(80))
> > state =Column(String(80))
> > country =Column(String
[sqlalchemy] Re: Dealing with large collections in a scaffolded application
I guess my question is: How can I efficiently load Patient and its related
Encounters? I have tried various loading strategies of dynamic, joined (I
would think this would be the desired option), subquery, no load, etc., and
it these do not load. On the other hand, I can load Encounter just fine and
access an instance of the Patient object.
On Tuesday, May 31, 2016 at 11:20:37 AM UTC-5, Horcle wrote:
>
> I have the following two models:
>
> Class Encounter(Model):
> __tablename__ = 'cp_service'
> id = Column(Integer, primary_key=True, autoincrement=True)
> master_service_id = Column(String(255))
> admission_datetime = Column(DateTime)
> admission_provider_id = Column(String(255))
> admission_status = Column(String(255))
> age_at_visit = Column(String(255))
> attending_provider_id = Column(String(255))
> center = Column(String(255))
> department_name = Column(String(255))
> discharge_datetime = Column(DateTime)
> encounter_category = Column(String(255))
> encounter_class = Column(String(255))
> encounter_date = Column(DateTime)
> encounter_setting = Column(String(255))
> encounter_type = Column(String(255))
> primary_care_provider_id = Column(String(255))
> primary_service_yn = Column(String(255))
> provider_id = Column(String(255))
> serv_area_id_orig = Column(String(255))
> service_id = Column(Integer)
> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
> patient = relationship("Patient", backref=backref("patientEncounter"),
> lazy='eager', primaryjoin="Patient.patient_id==Encounter.patient_id")
>
> class Patient(Model):
> __tablename__ = 'cp_patient'
> id = Column(Integer, primary_key=True, autoincrement=True)
> first_name = Column(String(100))
> middle_name = Column(String(80))
> last_name = Column(String(80))
> sex = Column(String(80))
> race = Column(String(80))
> birth_date = Column(DateTime)
> ethnicity = Column(String(80))
> race = Column(String(80))
> patient_id = Column(Integer)
> mrn = Column(String(80))
> title = Column(String(80))
> suffix = Column(String(80))
> name_alias = Column(String(80))
> addr_1 = Column(String(80))
> addr_2 = Column(String(80))
> addr_3 = Column(String(80))
> city = Column(String(80))
> state = Column(String(80))
> country = Column(String(80))
> zip = Column(String(80))
> encounter = relationship("Encounter", backref=backref("Patient"), lazy
> ='dynamic', primaryjoin="Patient.patient_id==Encounter.patient_id")
>
>
> Where one patient can have many encounters.
>
> I am able to load the patent model fine in a scaffolded list when I
> comment out the encounter relationship in the last line (there are roughly
> 35K patients), but when I link it to Encounters (1.7 million), then I am
> running into major performance issues. I tried using lazy='dynamic', but
> got an error that:
> On relationship Encounter.patient, 'dynamic' loaders cannot be used with
> many-to-one/one-to-one relationships and/or uselist=False.
>
> What other options are there available to not load all the relations until
> needed?
>
> 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Dealing with large collections in a scaffolded application
I have the following two models:
Class Encounter(Model):
__tablename__ = 'cp_service'
id = Column(Integer, primary_key=True, autoincrement=True)
master_service_id = Column(String(255))
admission_datetime = Column(DateTime)
admission_provider_id = Column(String(255))
admission_status = Column(String(255))
age_at_visit = Column(String(255))
attending_provider_id = Column(String(255))
center = Column(String(255))
department_name = Column(String(255))
discharge_datetime = Column(DateTime)
encounter_category = Column(String(255))
encounter_class = Column(String(255))
encounter_date = Column(DateTime)
encounter_setting = Column(String(255))
encounter_type = Column(String(255))
primary_care_provider_id = Column(String(255))
primary_service_yn = Column(String(255))
provider_id = Column(String(255))
serv_area_id_orig = Column(String(255))
service_id = Column(Integer)
patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
patient = relationship("Patient", backref=backref("patientEncounter"),
lazy='eager', primaryjoin="Patient.patient_id==Encounter.patient_id")
class Patient(Model):
__tablename__ = 'cp_patient'
id = Column(Integer, primary_key=True, autoincrement=True)
first_name = Column(String(100))
middle_name = Column(String(80))
last_name = Column(String(80))
sex = Column(String(80))
race = Column(String(80))
birth_date = Column(DateTime)
ethnicity = Column(String(80))
race = Column(String(80))
patient_id = Column(Integer)
mrn = Column(String(80))
title = Column(String(80))
suffix = Column(String(80))
name_alias = Column(String(80))
addr_1 = Column(String(80))
addr_2 = Column(String(80))
addr_3 = Column(String(80))
city = Column(String(80))
state = Column(String(80))
country = Column(String(80))
zip = Column(String(80))
encounter = relationship("Encounter", backref=backref("Patient"), lazy=
'dynamic', primaryjoin="Patient.patient_id==Encounter.patient_id")
Where one patient can have many encounters.
I am able to load the patent model fine in a scaffolded list when I comment
out the encounter relationship in the last line (there are roughly 35K
patients), but when I link it to Encounters (1.7 million), then I am
running into major performance issues. I tried using lazy='dynamic', but
got an error that:
On relationship Encounter.patient, 'dynamic' loaders cannot be used with
many-to-one/one-to-one relationships and/or uselist=False.
What other options are there available to not load all the relations until
needed?
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Padding columns to a query
Works perfectly!
Thanks!
Greg--
On Wednesday, February 17, 2016 at 11:48:55 AM UTC-6, Simon King wrote:
>
> On Wed, Feb 17, 2016 at 5:29 PM, Horcle <g...@umn.edu >
> wrote:
>
>> I have the following query db.session.query(label('sid',
>> distinct(Clinical.patient_sid))) to which I would like to pad a few
>> extra columns with constant values, like in the following SQL example
>>
>> select distinct(Clinical.patient_sid) as sid, 'stuph' as attribute
>>
>> from Clinical
>>
>>
>> I tried the naive approach of db.session.query(label('sid',
>> distinct(Clinical.patient_sid)),label('attribute', 'stuph')), but it
>> choked.
>>
>> I realize this is probably very simple, but alas.
>>
>>
> I think you can use literal_column for this, something like:
>
> import sqlalchemy as sa
> print sa.select([sa.literal_column("'stuph'").label('attribute')])
>
> output:
>
> SELECT 'stuph' AS attribute
>
> Beware that literal_column doesn't use bind parameters or do any escaping,
> so don't use it for untrusted input.
>
> 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Padding columns to a query
I have the following query db.session.query(label('sid',
distinct(Clinical.patient_sid))) to which I would like to pad a few extra
columns with constant values, like in the following SQL example
select distinct(Clinical.patient_sid) as sid, 'stuph' as attribute
from Clinical
I tried the naive approach of db.session.query(label('sid',
distinct(Clinical.patient_sid)),label('attribute', 'stuph')), but it choked.
I realize this is probably very simple, but alas.
TIA!
Greg--
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: use of replace
Looking for something to mimic the replace function in an SQL statement,
like:
Select * from Clinical where replace(Clinical.string_value, ' ' , '_')
On Monday, December 28, 2015 at 11:49:38 AM UTC-6, Horcle wrote:
>
> I am iteratively building a complex query. For one step, I have
>
> a[i] = a[i].filter(Clinical.string_value.op('=')([value[i]])).subquery()
>
>
> I would like to simply just replace the value in Clinical.string value
> such that all spaces are turned into underscores. My naive approach is to
> do this as
>
> replace(Clinical.string_value, ' ', '_'), but I know the syntax is invalid
> in this context. How do I plug this into my SQLAlch statement? I found the
> replace method here:
> http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func,
>
> but it does not seem to fit my use case.
>
> Thanks in advance!
>
> Greg--
>
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: use of replace
Looking for something like:
Select * from Clinical
where replace(Clinical.string_value, ' ', '_') = 'value_here'
On Monday, December 28, 2015 at 1:16:36 PM UTC-6, Horcle wrote:
>
> Or something along the lines of
>
> Clinical.string_value.replace(' ', '_').op...
>
>
>
> On Monday, December 28, 2015 at 11:49:38 AM UTC-6, Horcle wrote:
>>
>> I am iteratively building a complex query. For one step, I have
>>
>> a[i] = a[i].filter(Clinical.string_value.op('=')([value[i]])).subquery()
>>
>>
>> I would like to simply just replace the value in Clinical.string value
>> such that all spaces are turned into underscores. My naive approach is to
>> do this as
>>
>> replace(Clinical.string_value, ' ', '_'), but I know the syntax is
>> invalid in this context. How do I plug this into my SQLAlch statement? I
>> found the replace method here:
>> http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func,
>>
>> but it does not seem to fit my use case.
>>
>> Thanks in advance!
>>
>> Greg--
>>
>
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] use of replace
I am iteratively building a complex query. For one step, I have
a[i] = a[i].filter(Clinical.string_value.op('=')([value[i]])).subquery()
I would like to simply just replace the value in Clinical.string value such
that all spaces are turned into underscores. My naive approach is to do
this as
replace(Clinical.string_value, ' ', '_'), but I know the syntax is invalid
in this context. How do I plug this into my SQLAlch statement? I found the
replace method
here:
http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func,
but it does not seem to fit my use case.
Thanks in advance!
Greg--
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: use of replace
Or something along the lines of
Clinical.string_value.replace(' ', '_').op...
On Monday, December 28, 2015 at 11:49:38 AM UTC-6, Horcle wrote:
>
> I am iteratively building a complex query. For one step, I have
>
> a[i] = a[i].filter(Clinical.string_value.op('=')([value[i]])).subquery()
>
>
> I would like to simply just replace the value in Clinical.string value
> such that all spaces are turned into underscores. My naive approach is to
> do this as
>
> replace(Clinical.string_value, ' ', '_'), but I know the syntax is invalid
> in this context. How do I plug this into my SQLAlch statement? I found the
> replace method here:
> http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func,
>
> but it does not seem to fit my use case.
>
> Thanks in advance!
>
> Greg--
>
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: use of replace
Using MySQL. I was missing the 'func' attribute syntax for calling this in
my attempts. Works as desired.
Thanks!
Greg--
On Monday, December 28, 2015 at 9:19:22 PM UTC-6, Michael Bayer wrote:
>
> if you're using Postgresql you'd want to look into replace():
> http://www.postgresql.org/docs/9.3/static/functions-string.html
>
> e.g.
>
> query.filter(func.replace(MyClass.column, ' ', '_') == 'some_other_value')
>
> though if 'someothervalue' is an in-Python value and your database
> doesn't have underscores already, you might be able to get away with
> just using Python replace on that side, converting from underscore to
> space.
>
>
> On 12/28/2015 04:29 PM, Horcle wrote:
> > Looking for something to mimic the replace function in an SQL statement,
> > like:
> >
> > Select * from Clinical where replace(Clinical.string_value, ' ' , '_')
> >
> >
> > On Monday, December 28, 2015 at 11:49:38 AM UTC-6, Horcle wrote:
> >
> > I am iteratively building a complex query. For one step, I have
> >
> > a[i] =
> a[i].filter(Clinical.string_value.op('=')([value[i]])).subquery()
> >
> >
> > I would like to simply just replace the value in Clinical.string
> > value such that all spaces are turned into underscores. My naive
> > approach is to do this as
> >
> > replace(Clinical.string_value, ' ', '_'), but I know the syntax is
> > invalid in this context. How do I plug this into my SQLAlch
> > statement? I found the replace method
> > here:
> http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func
>
> > <
> http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func>,
>
>
> > but it does not seem to fit my use case.
> >
> > Thanks in advance!
> >
> > Greg--
> >
> > --
> > 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+...@googlegroups.com
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >.
> > To post to this group, send email to sqlal...@googlegroups.com
>
> > <mailto:sqlal...@googlegroups.com >.
> > Visit this group at https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] adjacency list to nested dictionary
I have the following SQLAlchemy class representing an adjacency list:
class Node(db.Model):
__tablename__ = 'meds'
id = Column(Integer, primary_key=True)
type = Column(String(64))
name = Column(String(64))
parent_id = Column(Integer, ForeignKey('node.id'))
children = relationship("Node")
I need to create a dictionary to represent a tree of arbitrary depth that
would look like:
{
"children": [
{
"children": [
{
"id": 4,
"name": "Child1",
"parent_id": 3,
"type": "Parent 2"
"children": [
{
"id": 6,
"name": "Child3",
"parent_id": 3,
"type": "Parent 3",
"children": [...]
},
{
"id": 7,
"name": "Child4",
"parent_id": 3,
"type": "Leaf"
}
]
},
{
"id": 5,
"name": "Child2",
"parent_id": 3,
"type": "Leaf"
}
],
"id": 3,
"name": "CardioTest",
"parent_id": null,
"type": "Parent"
}
]
}
Can this dictionary be built non-recursively? I am not sure how to manually
do this otherwise.
Thanks in advance!
Greg--
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] adjacency list to nested dictionary
We're using MySQL and need retrieval of all data from the table in the
format given (nested JSON). Simplest solution would be good (whether in app
or SQLAlchemy). I tried using the JsonSerializer as noted here
http://stackoverflow.com/questions/30367450/how-to-create-a-json-object-from-tree-data-structure-in-database,
but could not get it to work.
Thanks!
Greg--
On Wednesday, December 16, 2015 at 5:42:01 PM UTC-6, Jeff Widman wrote:
>
> What database are you using?
>
> Are you trying to solve data insert or retrieval?
>
> Do you want to do your traversal in your app or use SQLAlchemy to generate
> a SQL query that does all the work within the DB and then returns the
> result?
>
>
> ᐧ
>
> On Wed, Dec 16, 2015 at 1:28 PM, Horcle <g...@umn.edu >
> wrote:
>
>> I have the following SQLAlchemy class representing an adjacency list:
>>
>> class Node(db.Model):
>> __tablename__ = 'meds'
>> id = Column(Integer, primary_key=True)
>> type = Column(String(64))
>> name = Column(String(64))
>> parent_id = Column(Integer, ForeignKey('node.id'))
>> children = relationship("Node")
>>
>> I need to create a dictionary to represent a tree of arbitrary depth that
>> would look like:
>>
>>
>> {
>> "children": [
>> {
>> "children": [
>> {
>> "id": 4,
>> "name": "Child1",
>> "parent_id": 3,
>> "type": "Parent 2"
>> "children": [
>> {
>> "id": 6,
>> "name": "Child3",
>> "parent_id": 3,
>> "type": "Parent 3",
>> "children": [...]
>> },
>> {
>> "id": 7,
>> "name": "Child4",
>> "parent_id": 3,
>> "type": "Leaf"
>>}
>> ]
>> },
>> {
>> "id": 5,
>> "name": "Child2",
>> "parent_id": 3,
>> "type": "Leaf"
>> }
>> ],
>> "id": 3,
>> "name": "CardioTest",
>> "parent_id": null,
>> "type": "Parent"
>> }
>> ]
>> }
>>
>>
>> Can this dictionary be built non-recursively? I am not sure how to
>> manually do this otherwise.
>>
>> Thanks in advance!
>>
>> Greg--
>>
>> --
>> 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+...@googlegroups.com .
>> To post to this group, send email to sqlal...@googlegroups.com
>> .
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
>
> *Jeff Widman*
> jeffwidman.com <http://www.jeffwidman.com/> | 740-WIDMAN-J (943-6265)
> <><
>
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Bulk insert using bulk_insert_mappings
Sweet! It was the commit.
Thanks!
Greg--
On Thursday, December 3, 2015 at 10:48:45 AM UTC-6, Michael Bayer wrote:
>
>
>
> On 12/03/2015 11:28 AM, Horcle wrote:
> > I am trying to do a bulk insert of a large list of dictionaries of the
> form:
> >
> > results = [{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 1L},
> >{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 2L},
> >{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 3L},
> >{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 4L},
> >...
> > ]
> >
> > After reading about 'executemany' and bulk_insert_mappings, I decided to
> > try the later, since it looked much simpler to work with.
> >
> > Here is the code to execute this, with the naive assumption that this
> > would work with a list of dictionaries:
> >
> > Session = sessionmaker(bind=engine)
> > s = Session()
> > s.bulk_insert_mappings(Results,results)
> >
> > My Results model is:
> >
> > class Results(db.Model):
> > __tablename__ = 'results'
> > id = Column(Integer, primary_key=True, autoincrement=True)
> > sid = Column(Integer)
> > attribute = Column(String(2048))
> > value_s = Column(String(2048))
> > value_d = Column(Float)
> >
> > db is the SQLAlchemy object for my app:
> >
> > db = SQLAlchemy(app)
> >
> > No errors are thrown when I run this, but the data are not being
> inserted.
>
> 1. are you emitting s.commit() to commit your transaction?
>
> 2. what's in that "results" object? it should definitely do something
> unless the collection is empty.
>
> 3. turn on echo=True on your engine to see what SQL is emitted.
>
> Also note that the "SQLAlchemy(app)" object there is a flask thing, that
> provides its own Session. There's not much reason for it if you are
> making a Session() yourself with an "engine" from somewhere.
>
>
>
>
>
>
>
> >
> > End of naive assumption: The documentation says I need a "list of
> > mapping dictionaries." I assume since my dictionaries are key-value
> > pairs, I need to do something like
> >
> > dict = {k:v for k,v in (x.split(':') for x in results) }
> >
> > I tried this, but I then got an error that
> >
> > AttributeError: 'dict' object has no attribute 'split'
> >
> > Not sure where to go with this now...
> >
> > Thanks in advance!
> >
> > Greg--
> >
> > --
> > 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+...@googlegroups.com
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >.
> > To post to this group, send email to sqlal...@googlegroups.com
>
> > <mailto:sqlal...@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.
[sqlalchemy] Bulk insert using bulk_insert_mappings
I am trying to do a bulk insert of a large list of dictionaries of the form:
results = [{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 1L},
{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 2L},
{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 3L},
{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 4L},
...
]
After reading about 'executemany' and bulk_insert_mappings, I decided to
try the later, since it looked much simpler to work with.
Here is the code to execute this, with the naive assumption that this would
work with a list of dictionaries:
Session = sessionmaker(bind=engine)
s = Session()
s.bulk_insert_mappings(Results,results)
My Results model is:
class Results(db.Model):
__tablename__ = 'results'
id = Column(Integer, primary_key=True, autoincrement=True)
sid = Column(Integer)
attribute = Column(String(2048))
value_s = Column(String(2048))
value_d = Column(Float)
db is the SQLAlchemy object for my app:
db = SQLAlchemy(app)
No errors are thrown when I run this, but the data are not being inserted.
End of naive assumption: The documentation says I need a "list of mapping
dictionaries." I assume since my dictionaries are key-value pairs, I need
to do something like
dict = {k:v for k,v in (x.split(':') for x in results) }
I tried this, but I then got an error that
AttributeError: 'dict' object has no attribute 'split'
Not sure where to go with this now...
Thanks in advance!
Greg--
--
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] Expanded view of query parameters
Dear all, How can I expand the output to show ALL query parameters. For example, I have the following: 2015-07-09 09:51:09,478 INFO sqlalchemy.engine.base.Engine (['test_code'], ['13457-7'], ['result_value_num'], '160', '160', ['1970-01-01'], ['blood_pressure'], ['blood_pressure'] ... displaying 10 of 17 total bound parameter sets ... 'M', ['1970-01-01']) But, instead of displaying 10 of 17 parameters, I would like to see all of them. Thanks in advance! Greg-- -- 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] Trying to join a session query to a union
Hi all,
I have the following union query:
q1 = db.session.query(label('sid',distinct(left.c.patient_sid)))
q2 = db.session.query(label('sid',distinct(right.c.patient_sid)))
query = q1.union_all(q2)
Which works just fine.
And I have the following query to which I would like to join this:
intersect = db.session.query(label('sid', distinct(other.c.patient_sid)))
q = intersect.join(query, query.c.sid, intersect.c.sid)
But, alas, I keep getting:
AttributeError: 'Query' object has no attribute 'c' (print query.c.sid
gives the same thing and other.c.patient_sid gives an object value)
How can I join the union to the intersect query? I would use the intersect
method, but MySQL sadly does not offer this easy out.
I tried union_all as a selectable, but I need to pass these query objects
around and do some conditional logic on them and this caused a ton of
problems
Thanks very much!
Greg--
--
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] Re: Trying to join a session query to a union
For the record, this should have a '==' for the join condition: q = intersect.join(query_select, query_select.c.sid == intersect.c.sid) On Wednesday, July 8, 2015 at 7:48:54 PM UTC-5, Horcle wrote: Weird, I had tried using the subquery() method earlier, but it didn't work. Not sure why, but now it is returning the desired query object. (I guess stepping away from this for a few hours was a good idea, eh?!) Thanks! Greg-- On Wednesday, July 8, 2015 at 6:24:16 PM UTC-5, Jonathan Vanasco wrote: you probably need to modify the various objections with a `.select()` or `.subquery()` e.g: query_select = query.select() q = intersect.join(query_select, query_select.c.sid, intersect.c.sid) pay attention to the docs on what the various methods return. some return a selectable, others don't. you can usually toggle around the different forms with .select, .subquery, .alias (and there are a few others) -- 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] Re: Trying to join a session query to a union
Weird, I had tried using the subquery() method earlier, but it didn't work. Not sure why, but now it is returning the desired query object. (I guess stepping away from this for a few hours was a good idea, eh?!) Thanks! Greg-- On Wednesday, July 8, 2015 at 6:24:16 PM UTC-5, Jonathan Vanasco wrote: you probably need to modify the various objections with a `.select()` or `.subquery()` e.g: query_select = query.select() q = intersect.join(query_select, query_select.c.sid, intersect.c.sid) pay attention to the docs on what the various methods return. some return a selectable, others don't. you can usually toggle around the different forms with .select, .subquery, .alias (and there are a few others) -- 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] use of between() with Column.op()
I really dig use of the column operator for constructing queries, but have been unsuccessful with using this when the argument is between. I read somewhere that Column.op(var) when var = in_ does not work, so I would assume that this is true with between. Am I doing something wrong, or is this an expected behavior? If so, is there a more general way to deal with evaluating all passed column operators so that I don't have to have separate conditions for the between and in operators? Thanks in advance! Greg-- -- Greg M. Silverman Senior Developer Analyst Cardiovascular Informatics http://www.med.umn.edu/cardiology/ University of Minnesota -- 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] use of between() with Column.op()
The missing '_and' was it. Thanks!
The reason why I am loathe to use between() (which I am doing in the
interim) is that I have a generalized query structure to which I pass ad
hoc elements based on user selection. If I can somehow deal with
dynamically constructing the arguments for the between operator
(specifically the 'and_' operator; the columns are easy) then this will
keep my desired structure. Otherwise, I am stuck with use of a conditional
to evaluate if between() is being selected.
Greg--
On Friday, April 10, 2015 at 9:55:29 PM UTC-5, Michael Bayer wrote:
On 4/10/15 10:23 PM, Horcle wrote:
I really dig use of the column operator for constructing queries, but have
been unsuccessful with using this when the argument is between. I read
somewhere that Column.op(var) when var = in_ does not work, so I would
assume that this is true with between.
between is a three-op, that is, it is x BETWEEN a AND b. op() isn't
built with that in mind but you can always chain, such as:
print column('x').op('BETWEEN')(and_(column('a'), column('b')))
x BETWEEN a AND b
depends on what you want to do really (why not just use between() ?)
Am I doing something wrong, or is this an expected behavior? If so, is
there a more general way to deal with evaluating all passed column
operators so that I don't have to have separate conditions for the between
and in operators?
Thanks in advance!
Greg--
--
Greg M. Silverman
Senior Developer Analyst
Cardiovascular Informatics http://www.med.umn.edu/cardiology/
University of Minnesota
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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.
[sqlalchemy] Re: Dynamically constructing joins
eval() was definitely not doing what I expected. Thanks for the tip about getattr(), and thanks for helping get my head screwed on right! Greg-- On Wednesday, March 25, 2015 at 11:33:44 AM UTC-5, Jonathan Vanasco wrote: Yeah, there's no reason to touch eval -- and a lot of reasons not to. Security issues aside, when you make a mistake the error will be completely unintelligible. You can create joins dynamically very easily by just iteratively building up on it, and using getattr() if needed. If you're doing any advanced things (subqueries, aliases, etc), I would suggest keeping the online docs loaded in a browser window and paying close attention to the return values. Most operations will return a query, but a few will return another object. -- 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] Dynamically constructing joins
I have a situation where I can have an arbitrary number of subqueries that
need to be joined on the last step, except if the number of queries, n, is
1.
For example, for n = 1, suppose I have a complex query set to the variable
A[1]
The final submitted query would then look like:
query = db.session.query(label('sid',
distinct(A[1].c.patient_sid)))
Easy enough!
Now, suppose, I have two complex queries, A[1] and A[2] that are then
joined as such:
query = db.session.query(label('sid',
distinct(A[1].c.patient_sid))). \
join(A[2],A[2].c.patient_sid==a[1].c.patient_sid)
Not too bad...
Now, I have an arbitrary number of complex queries, A[1]...A[n] that need
to be joined:
query = db.session.query(label('sid',
distinct(A[1].c.patient_sid))). \
join(A[2],A[2].c.patient_sid==a[1].c.patient_sid). \
join(A[n],A[n].c.patient_sid==a[1].c.patient_sid)
The above works fine, when I have conditionals based on the number n of
queries, e.g.,
if (n == 1):
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid)))
if (n == 2):
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid))). \
join(a[2],a[2].c.patient_sid==a[1].c.patient_sid)
if (n == 3):
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid))). \
join(a[2],a[2].c.patient_sid==a[1].c.patient_sid). \
join(a[3],a[3].c.patient_sid==a[1].c.patient_sid)
etc., but since I can have an arbitrary number of these queries that need
to be joined, not only is use of conditionals to set up the correct form of
my join inefficient, it is highly redundant and would be a huge mess.
Thus, I am wondering if it is possible to construct my join somehow, like:
if (n == 1):
query = 'db.session.query(label('sid',
distinct(a[1].c.patient_sid)))'
elif (n 1):
query = 'db.session.query(label('sid',
distinct(a[1].c.patient_sid)))'
for i in range (0,n)
query += '.join(A[i],A[i].c.patient_sid==a[1].c.patient_sid)'
eval(query)
I've tried all sorts of crazy things, but cannot get the general case to
work.
Thanks in advance!
Greg--
--
Greg M. Silverman
Senior Developer Analyst
Cardiovascular Informatics http://www.med.umn.edu/cardiology/
University of Minnesota
--
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] Issue with return results
Unfortunately, dumping SQL Server (in favor of Oracle) may not be an
option, due to management concerns and other factors. Still working on it.
However, I did manage to get this working with pymssql. Apparently, there
is a bug with pyodbc and 64-bit python
(see
https://community.vertica.com/vertica/topics/mac_pyodbc_string_encoding_issue).
So, short of applying the fix to the cpp file recompiling pyodbc (see
http://www.vertica-forums.com/viewtopic.php?f=35t=1863p=6174#p6174), the
easy solution is to use pymssql. I believe the upgrade I did from Lion to
Mavericks allows use of 64-bit python now, so this makes sense.
Thanks!
Greg--
On Thursday, September 4, 2014 9:20:53 PM UTC-5, Horcle wrote:
I think I am going to dump SQL Server and just go with Postgres. Much
easier, and less of a headache. Fortunately, we are not yet in production.
Thanks!
Greg--
Thanks. I forgot to mention that I had tried adding the encoding scheme to
freetds.conf. I also tried other encoding schemes, all to no avail. I may
try pymssql tomorrow to see what that does. I would have tried mxodbc, but
I am not about to pay $379 for a driver. I may also see if I can get the MS
ODBC driver for Linux to work on my Mac.
I have to say that the MS SQL stuff is a royal PITA, but unfortunately,
that is what I am stuck with at work. Uggh. (;_;)
The version of FreeTDS I have been using has always been 9.1 (although, I
noticed that the Brew formula for it changed in the last few days to 9.1_1,
for what that's worth).
Greg--
On Thursday, September 4, 2014 5:23:02 PM UTC-5, Michael Bayer wrote:
SQL Server and unix, many things can change:
- UnixODBC version
- FreeTDS version (0.82 and 0.91 have *extremely* different behaviors)
- FreeTDS configuration
The first place I’d look in this case would be your freetds.conf, you
probably need to configure the character set correctly in there.
On Sep 4, 2014, at 5:06 PM, Horcle g...@umn.edu wrote:
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col (
'default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo',
)
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7
as [Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col (
'Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT [
TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]WHERE [TABLES_1].[
TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[TABLE_TYPE] = ?
ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col (
'TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd'
, )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine
[sqlalchemy] Issue with return results
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col (
'default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as [
Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT [TABLES_1
].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[
TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col (
'TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irb_desc'
, )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_status', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_status_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_subject', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'subj_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'subj_status_desc', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row (u
'subject_status', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row (u
'sysdiagrams', )
Now, in my updated environment, it looks like this:
python test_connect.py
2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Row
(u'd\x00b\x00o\x00', )
2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x10fda1510
2014-09-04 15:26:06,000 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-09-04 15:26:06,001 INFO sqlalchemy.engine.base.Engine ()
2014-09-04 15:26:06,002 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-09-04 15:26:06,002 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-09-04 15:26:06,007 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-09-04
Re: [sqlalchemy] Issue with return results
Thanks. I forgot to mention that I had tried adding the encoding scheme to
freetds.conf. I also tried other encoding schemes, all to no avail. I may
try pymssql tomorrow to see what that does. I would have tried mxodbc, but
I am not about to pay $379 for a driver. I may also see if I can get the MS
ODBC driver for Linux to work on my Mac.
I have to say that the MS SQL stuff is a royal PITA, but unfortunately,
that is what I am stuck with at work. Uggh. (;_;)
The version of FreeTDS I have been using has always been 9.1 (although, I
noticed that the Brew formula for it changed in the last few days to 9.1_1,
for what that's worth).
Greg--
On Thursday, September 4, 2014 5:23:02 PM UTC-5, Michael Bayer wrote:
SQL Server and unix, many things can change:
- UnixODBC version
- FreeTDS version (0.82 and 0.91 have *extremely* different behaviors)
- FreeTDS configuration
The first place I’d look in this case would be your freetds.conf, you
probably need to configure the character set correctly in there.
On Sep 4, 2014, at 5:06 PM, Horcle g...@umn.edu javascript: wrote:
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col (
'default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result'
,)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT [
TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]WHERE [TABLES_1].[
TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[TABLE_TYPE] = ?
ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col (
'TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd',
)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row span
style=color: #660; class=st
...
--
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] cannot access tables
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu javascript:
wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method (that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some schema name setting or something like that.
Thanks, I did not realize this was an option (actually, it is echo=True,
but at least I can see the SQL being sent). Hopefully this will lead me to
an answer.
Greg--
--
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] cannot access tables
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo', 'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu javascript:
wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I
ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method
(that
is '42' is printing as expected), but when I run the
inspector.get_table_names method with the given conditional it is
printing
the 'A' (I have tried other table names in the same database to which I
added 'irbd_balance,' all with the same result.
what is the SQL output if you set echo=‘debug’; then, take the SQL
you
see and take a look at what it’s SELECTing so you can see what might be
wrong. Probably some schema name setting or something like that.
Thanks, I did not realize this was an option (actually, it is echo=True,
but
at least I can see the SQL being sent). Hopefully this will lead me to
an
answer.
echo='debug' will show you more information than echo=True (it
shows the rows coming back as well as the query that is sent)
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] cannot access tables
Thanks, this does help. I was wondering why the return results had no
values given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are no matching rows.
Then a bit further along, SA is running this query:
SELECT [TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
...passing the parameters dbo and BASE TABLE, but not getting any
results back.
I don't know anything about MS-SQL so can't tell you why that is, but
perhaps you've got enough information to carry on digging?
Hope that helps,
Simon
On Mon, Aug 18, 2014 at 4:27 PM, Horcle g...@umn.edu javascript:
wrote:
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo',
)
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 * 7
as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col
('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo',
'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu wrote:
Then, I thought, what if this is an SQLAlchemy issue. Looks to be. I
ran
the following script as a test:
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=ip_address;'
'DATABASE=db_name;UID=test;PWD=test;port=1433;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A'
Again, I am connecting fine with the database create.engine method
Re: [sqlalchemy] cannot access tables
Thanks for the heads up. Unfortunately, it did not help. In any case, the
issue appears to be that while the last query DOES return a record set when
run as straight up SQL on the server, it does not work as desirecd through
SQLAlchemy.
More digging to be done.
Greg--
On Monday, August 18, 2014 11:30:39 AM UTC-5, Simon King wrote:
It looks like the code that runs the SELECT default_schema_name
query has changed since the version you are running:
https://bitbucket.org/zzzeek/sqlalchemy/commits/1fb4ad75a38c
It might be worth upgrading to the latest release.
Simon
On Mon, Aug 18, 2014 at 5:22 PM, Horcle g...@umn.edu javascript:
wrote:
Thanks, this does help. I was wondering why the return results had no
values
given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name',)
...but there are no matching rows.
Then a bit further along, SA is running this query:
SELECT [TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
...passing the parameters dbo and BASE TABLE, but not getting any
results back.
I don't know anything about MS-SQL so can't tell you why that is, but
perhaps you've got enough information to carry on digging?
Hope that helps,
Simon
On Mon, Aug 18, 2014 at 4:27 PM, Horcle g...@umn.edu wrote:
Indeed!
Here is the output:
gms$ python test_connect.py
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row
(u'dbo',
)
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101839490
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6 *
7
as
[Result];
2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col
('Result',)
2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42,
)
42
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo',
'BASE
TABLE')
2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
A
Not quite sure how to parse this?
For example, is u'dbo' what is being used as the schema?
Greg--
On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
On Mon, Aug 18, 2014 at 3:47 PM, Horcle g...@umn.edu wrote:
On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
On Aug 15, 2014, at 5:03 PM, Greg Silverman g...@umn.edu
wrote:
Then, I thought, what if this is an SQLAlchemy issue
Re: [sqlalchemy] cannot access tables
It ended up being a unicode issue. I had to set this:
supports_unicode_binds=False (see Unicde Binds here
http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc),
in order to get it to work. Annoying, to say the least!
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
(u'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_subject', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_status_desc', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'subject_status', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'sysdiagrams', )
Thanks all!
Greg--
On Monday, August 18, 2014 1:08:55 PM UTC-5, Michael Bayer wrote:
try the query as stated along with a pyodbc connection (e.g. conn =
pyodbc.connect(…); cursor = conn.cursor(); cursor.execute(the statement);
cursor.fetchall()). the way pyodbc is connecting might be changing things.
On Aug 18, 2014, at 12:59 PM, Horcle g...@umn.edu javascript: wrote:
Thanks for the heads up. Unfortunately, it did not help. In any case, the
issue appears to be that while the last query DOES return a record set when
run as straight up SQL on the server, it does not work as desirecd through
SQLAlchemy.
More digging to be done.
Greg--
On Monday, August 18, 2014 11:30:39 AM UTC-5, Simon King wrote:
It looks like the code that runs the SELECT default_schema_name
query has changed since the version you are running:
https://bitbucket.org/zzzeek/sqlalchemy/commits/1fb4ad75a38c
It might be worth upgrading to the latest release.
Simon
On Mon, Aug 18, 2014 at 5:22 PM, Horcle g...@umn.edu wrote:
Thanks, this does help. I was wondering why the return results had no
values
given.
Greg--
On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
I think you are seeing, for each query:
1. The query itself
2. The parameters being passed in to the query
3. The names of the columns being returned
4. The returned rows, if any.
So for example, the first thing that happens is:
SELECT user_name()
with no parameters
()
returning a result set containing a single column with no name:
('',)
followed by a single row containing a single value, the unicode string
dbo:
(u'dbo', )
The second query goes like this:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
Note the bound parameter (name = ?). The next line tells us that SA
is sending the unicode string dbo for that parameter:
(u'dbo',)
The result set has a single column called 'default_schema_name':
('default_schema_name
