Empty [] using objects.all() on legacy database

[geraldcor]

Hello all,

I am trying to work with a legacy database and I ran inspectdb and
have a model with the 2 tables that I want to use. The first table,
the main table is not working at all. The second table which is
related via a foreign key seems to be working just fine. Here are the
symptoms:

In the shell I import my tables, to c=Customers.objects.all()
c
[] which is an empty dictionary or tuple (sorry for the confusion)

however if I do t=Tblcontacts.objects.all()
t
[big long list of Tblcontacts objects] and I can even utilize this
data in a template via a view.

I can't figure out why one would work while the other does not.

here are the first view field definitions in my model in case that
offers any insight.

#The customers table that doesn't work.
class Customers(models.Model):
idflcustomernum = models.AutoField(primary_key=True,
db_column='IDFLCustomerNum') # Field name made lowercase.
typenum = models.IntegerField(null=True, db_column='TypeNum',
blank=True) # Field name made lowercase.
type = models.CharField(max_length=255, db_column='Type',
blank=True) # Field name made lowercase.
billto = models.IntegerField(null=True, db_column='BillTo',
blank=True) # Field name made lowercase.

#The contacts table that works
class Tblcontacts(models.Model):
contactnumber = models.AutoField(primary_key=True,
db_column='ContactNumber') # Field name made lowercase.
clientid = models.ForeignKey(Customers, db_column='ClientID') #
Field name made lowercase.
contactname = models.CharField(max_length=50,
db_column='ContactName', blank=True) # Field name made lowercase.
title = models.CharField(max_length=50, db_column='Title',
blank=True) # Field name made lowercase.
salutation = models.CharField(max_length=50,
db_column='Salutation', blank=True) # Field name made lowercase.

Thank you for all past and future help

Greg

[geraldcor]

Ok, so I feel a bit silly, but it was because I had a column name in
my db called Discount% and I am assuming the % is screwing it up.

Now the question becomes how do I escape the % or do I have to rename
my db column (please god not the latter as that would entail a whole
mess of rewriting stuff). I already tried some unicode stuff but I'm
fairly untrained in that area. Any ideas?

Thanks

Greg

[Karen Tracey]

On Fri, Jun 19, 2009 at 4:41 PM, geraldcor <greg...@gmail.com> wrote:

Ok, so I feel a bit silly, but it was because I had a column name in
my db called Discount% and I am assuming the % is screwing it up.

Now the question becomes how do I escape the % or do I have to rename
my db column (please god not the latter as that would entail a whole
mess of rewriting stuff). I already tried some unicode stuff but I'm
fairly untrained in that area. Any ideas?

Interesting.  Try doubling the % when you specify it in db_column.  I rather think you shouldn't need to do that, though, so this may be a bug in Django.  BTW, what DB?

Karen

[Greg Corey]

I will try that tomorrow and let you know.

The DB is an old Microsoft Access database that was then migrated to MySQL. It was designed by a definite novice so it has quirks, but it still pumps along. It is a DB of testing results for our small laboratory.

Greg

[Greg Corey]

Karen, the double % ( i.e. 'discount%%') worked. Is this a big enough bug that it should be reported? I'm not sure what is a bug and what is just my ignorance. Thank you sooo much for the help.

Greg

[Erik Vorhes]

On Mon, Jun 22, 2009 at 12:52 PM, Greg Corey<greg...@gmail.com> wrote:
> Karen, the double % ( i.e. 'discount%%') worked. Is this a big enough bug
> that it should be reported? I'm not sure what is a bug and what is just my
> ignorance. Thank you sooo much for the help.

The first % escapes the second % sign, and thus allows for the actual
symbol to display. This is because Python uses % to include variables,
etc., into a string. So it's not a bug at all.

Erik Vorhes

[Greg Corey]

I figured it wasn't a bug and I just had to do the right escape sequence, but Karen mentioned that it might be a bug. Guess not. Hope this helps someone else in the future.

Greg

[Karen Tracey]

But we're talking about the the db_column parameter for a model field definition here.  I don't see that it's at all obvious that one should need to escape % symbols there.  I see how down in the depths of passing SQL to the database (for the one backend I looked at, MySQL) it needs to have been done, when args are being passed with the SQL, because the DB backend is going to use string interpolation to place the args into the SQL and if the % in the column name hasn't been escaped there won't be enough actual passed args  for the interpolation. 

But I don't know if that code is the same for all backends, nor do I know if Django consistently uses and empty tuple for the no-args case vs. passing None for args.  If it ever does the latter, doubling the % in the db_column specification won't work because when None is passed for args string interpolation isn't done, and the doubled % would be passed as the column name. 

All in all I think it would be good to open a ticket to track this.  I don't have time to look into it any more deeply right now.

Karen

[Greg Corey]

Ok. I will work on opening a ticket based on your recommendation, though I will not have your depth of analysis on the problem. Mind if I quote your most recent explanation if needed?

Greg

[Karen Tracey]

On Tue, Jun 23, 2009 at 12:04 PM, Greg Corey <greg...@gmail.com> wrote:

Ok. I will work on opening a ticket based on your recommendation, though I will not have your depth of analysis on the problem. Mind if I quote your most recent explanation if needed?

Sure, but it's probably not necessary, though you could include a pointer to this thread in the Google group's archive for the group for reference purposes.  For the ticket description all you need to do is describe the problem from the user's level.  You've got an existing database with a table that has a column name with a % in it.  Unless you double the % when you specify it in db_column for the associated Django model field, you cannot access data in this table via your Django model.  Needing to double the % in this parameter is entirely non-obvious and (I'm assuming -- you might want to verify this) not documented, so it either should not be necessary or needs to be documented.  (If in fact you find that the description of db_column somewhere says %s in column names need to be doubled, then never mind about a ticket...but I'd be surprised if that were the case.)

Karen