Im trying to query the second DB for information. Its a back up from
another database, its separate from default, here is the settings.py
-> DATABASES:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': '#',
'USER': '#',
'PASSWORD': '#',
'HOST': '',
'PORT': '',
},
'userxml': {
'ENGINE': 'django.db.backends.mysql',
'NAME': '#',
'USER': '#',
'PASSWORD': '#',
'HOST': '',
'PORT': '',
},
'DataDump': {
'ENGINE': 'django.db.backends.mysql',
'NAME': '#',
'USER': '#',
'PASSWORD': #',
'HOST': '',
'PORT': '',
}
}
So the two tables are in DataDump DB. So what im trying to do is
DataDump --> table1(field) compare with table2(field). I hope this
makes it more clear, the DB is already populated with values, i just
need to compare couple of tables. Im not going to write anything to
the table, just going to use it for reads.
>From your example i came up with this (the SmallInteger is ok because
the values go up only a couple of thousand):
class itemSearch(models.Model):
invgroupstable = models.ForeignKey('invGroups', db_column =
'categoryID')
invtypestable = models.ForeignKey('invTypes', db_column = 'typeName')
class invGroups(models.Model):
groupID = models.SmallIntegerField(primary_key = True)
categoryID = models.SmallIntegerField()
class Meta:
db_table = 'invGroups'
class invTypes(models.Model):
typeID = models.SmallIntegerField(primary_key = True)
typeName = models.CharField(max_length = 200)
published = models.SmallIntegerField()
groupID = models.SmallIntegerField()
class Meta:
db_table = 'invTypes'
And the query is like this:
item_search_results = itemSearch.objects.using(
'DataDump'
).filter(
invgroupstable__categoryid__in[7, 8, 9, 18, 20],
invtypestable__typename_ilike = search_query
)[0:15]
And i get this error,
global name 'invgroupstable__categoryid__in' is not defined
I think im really miss understanding something here.
On Thu, Dec 22, 2011 at 2:57 PM, Tom Evans <tevans...@googlemail.com> wrote:
> On Thu, Dec 22, 2011 at 12:27 PM, kr0na <enarbu...@gmail.com> wrote:
>> Im trying to compare two tables in a second database that is a
>> migrated database from mssql. The second database doesn't have any
>> apps for it, it will only be used for queries.
>>
>> This are my models for the two tables.
>> from django.db import models
>>
>> class invGroups(models.Model):
>> groupID = models.SmallIntegerField(primary_key = True)
>> categoryID = models.SmallIntegerField()
>>
>> def __unicode__(self):
>> return self.item
>>
>> class Meta:
>> db_table = 'invGroups'
>>
>> class invTypes(models.Model):
>> typeID = models.SmallIntegerField(primary_key = True)
>> typeName = models.CharField(max_length = 200)
>> published = models.SmallIntegerField()
>> groupID = models.SmallIntegerField()
>>
>> def __unicode__(self):
>> return self.item
>>
>> class Meta:
>> db_table = 'invTypes'
>>
>> And the query so far.
>> item_search_results = invTypes.objects.using(
>> 'DataDump'
>> ).filter(
>> typeName__icontains = search_query
>> )[0:15]
>>
>> I currently can select from only one database, and the query is what i
>> have so far. I tried to use ForeignKey with no results.
>
> I didn't really understand what you are asking here. Where does the
> second database come into it?
>
>> Can I do this
>> without using a RAW query? Im trying to achieve this query:
>> SELECT
>> typeName
>> FROM
>> invGroups,
>> invTypes
>> WHERE
>> invTypes.groupID = invGroups.groupID and
>> invGroups.categoryID in (7, 8, 9, 18, 20) and
>> invTypes.typeName like 'query%'
>> ORDER BY
>> invTypes.typeName;
>>
>
> First off, currently your objects are not related to each other. You
> need to specify a foreign key relationship between the two related
> models:
>
> class invTypes(models.Model):
> typeID = models.SmallIntegerField(primary_key = True)
> typeName = models.CharField(max_length = 200)
> published = models.SmallIntegerField()
> group = models.ForeignKey('invGroups', db_column='groupID')
>
> Then, you can simply use Django's ORM to query the tables:
>
> invType.objects.filter(invgroups__categoryid__in[7,8,9,8,20],
> typename__ilike=query).values_list('typename', flat=True)
>
> Django will do an explicit join, rather than the implicit one in your query.
>
> There are a few other things I find strange with your code:
>
> Readability: your class names should be UpperCamelCase and your
> variable, function and method names either lowerCamelCase or
> lower_case_with_underscores. PEP 8 (and me) prefer the latter.
>
> Straight up bugs: Both model's unicode methods refer to self.item, but
> there is no self.item.
>
> Specifying the type and name of your primary key. Django will auto add
> a primary key with a sane type if you don't. You've chosen
> SmallIntegerField - in most databases, this will have a maximum value
> of 32,768, meaning you can put that many items in your DB table and no
> more.
>
> It could be you are mapping Django onto an existing DB structure,
> which would explain the strange model attribute names. When doing
> this, it's easier to have a sensible python name for the attribute,
> and specify the actual column name in the db_column argument, as I did
> adding the ForeignKey.
>
> Cheers
>
> Tom
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
