Re: Compare two tables in a secondary database
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 wrote:
> On Thu, Dec 22, 2011 at 12:27 PM, kr0na 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
> ther
Re: Compare two tables in a secondary database
Yeah, the ORM didn't work out as i thought for this. I have written this raw query with a model. Alot less code compared to as before. class itemSearch(models.Model): typeID = models.SmallIntegerField(primary_key = True)typeName = models.CharField(max_length = 200) item_search_results = itemSearch.objects.raw( '''SELECT * FROM invTypes WHERE invTypes.typeName LIKE '%s%' LIMIT 0, 10''', [search_query] ).using( 'DataDump' ) for name in item_search_results: results.append(name.typeName) I get this error. "not enough arguments for format string", which im guessing the LIKE isnt working because this query works. item_search_results = itemSearch.objects.raw( '''SELECT * FROM invTypes LIMIT 0, 10''' ).using( 'DataDump' ) for name in item_search_results: results.append(name.typeName) I have no idea what could be wrong in the query or the model. Any help? On Fri, Dec 23, 2011 at 1:07 AM, Python_Junkie wrote: > I think you are limiting yourself by excluding sql. > > I use the ORM for the admin side of the house and I use sql for all of > my web based presentations. > > SQL will do everything that you want to achieve. > > ORM has limitations as you have seen. > > On Dec 22, 7:27 am, kr0na 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. 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; > > -- > 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.
Re: Compare two tables in a secondary database
You are right. Just as i got the same result I saw your post. And this: > Second, MySQLdb quotes parameters before putting them into the > placeholder, so you'd end up with > > ... like ''something'%' I saw that happen in the query ^_^ This is the final code for it: class item_search(models.Model): typeID = models.SmallIntegerField(primary_key = True) typeName = models.CharField(max_length = 200) search_query = request.GET[u'query'] item_search_results = item_search.objects.raw( "\ SELECT \ typeID, typeName \ FROM \ invGroups, \ invTypes \ WHERE \ invTypes.groupID = invGroups.groupID AND \ invGroups.categoryID IN (7, 8, 18, 20) AND \ invTypes.typeName LIKE %s AND \ invTypes.published = 1 \ ORDER BY \ typeName \ LIMIT 0, 10", ['%' + search_query + '%'] ).using( 'DataDump' ) for name in item_search_results: results.append(name.typeName) When i wrote it i modified the search_query before the actual raw query. But i like how you did it in the params, one less line of code :) . Thanks for helping me, i appreciate it greatly. :) On Fri, Dec 23, 2011 at 5:44 PM, Dennis Lee Bieber wrote: > On Fri, 23 Dec 2011 03:33:14 +0200, Edvinas Narbutas > wrote: > > >>item_search_results = itemSearch.objects.raw( >> '''SELECT * FROM invTypes WHERE invTypes.typeName LIKE '%s%' >>LIMIT 0, 10''', [search_query] >>).using( >> 'DataDump' >>) > > >>I get this error. "not enough arguments for format string", which im >>guessing the LIKE isnt working because this query works. >> > > Presuming the interface 1) uses %s for parameter placeholder and 2) > safely escapes/quotes the parameters, then you need to reformulate that > query. (Oh, and did you want SQL wildcard % on both sides of the > parameter?) > > MySQLdb, which uses %s, would probably complain about your shown > example for a number of reasons... > > First, you have '%s%', and that second % is not escaped -- so it > will be seen as the start of a second placeholder rather than a literal > % in the result. You'd need '%s%%' to have a single % at the end of the > parameter. > > Second, MySQLdb quotes parameters before putting them into the > placeholder, so you'd end up with > > ... like ''something'%' > > Note the quotes in the result -- those are single quotes, not a > double quote at the beginning; so you have the "like" term being an > empty string followed by garbage.. > > Reformulated to fit the MySQLdb parameter handling you should use > something like: > > "select * from invTypes where invTypes.typeName like %s limit 0, 10", > ["%" + search_query + "%"] > > wherein all modification of the "search_query" (prepending/appending SQL > % wildcards) is done to the parameter, NOT the placeholder, since you > need them inside the quoting that the adapter provides; and NO quotes > around the %s placeholder. > -- > Wulfraed Dennis Lee Bieber AF6VN > wlfr...@ix.netcom.com HTTP://wlfraed.home.netcom.com/ > > -- > 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.
