If using South, does it even matter what the underlying database is?

[Houmie]

Today I have started my first steps into postgresql, since its recommended by the Django team.

I came across several issues, that I solved patiently one by one.

1) Creating tables under postgresql requires to login as a different OS login, from which you don't even know the password.  Fine, I found the solution and created the database.

2) After running syncdb, you can't simply execute a simple insert sql like this:

    INSERT INTO App_contacttype (contact_type, company_id) VALUES ('Buyer', 1),('Seller', 1);

Since Django creates it with quotes the table becomes case sensitive, hence it has to be like this:

    INSERT INTO "App_contacttype" (contact_type, company_id) VALUES ('Buyer', 1),('Seller', 1);

But the problems seem never to end. Now suddenly the execution of the insert script says

    ERROR: value too long for type character varying(40)
    SQL state: 22001

In MySQL this was no problem. I don't know, right now I am getting a bit of cold feet, maybe I should just stick to MySQL.

The only reason I was considering postgresql was that some research suggested postgresql has much better support for changing Schemas along the way than MySQL. 

However considering http://south.aeracode.org/ would take away all the pain of syncing Schemas, would I even need to worry about Schema changes at all no matter what the underlying database is?

[Thomas Lockhart]

On 12-07-06 8:56 AM, Houmie wrote:
> Today I have started my first steps into postgresql, since its
> recommended by the Django team.
> I came across several issues, that I solved patiently one by one.

(disclaimer: I'm a Postgres homer)

These issues *should* come up with a well-designed database.

> 1) Creating tables under postgresql requires to login as a different
> OS login, from which you don't even know the password. Fine, I found
> the solution and created the database.

I'm guessing you are not there yet.

If using a system-supplied Postgres installation, there is likely a
pre-defined Postgres user. Some installations set this up as a nologin
account. But you can "su postgres" to have the right permissions to
start creating users (or roles) in the database.

You should define a user other than the postgres user to own the
database for your Django installation. Use the "createuser" command as
the postgres user, and make your first one the admin user for your
databases (give it the superuser permissions; createuser will ask you
about that). From then on you can use that account to interact with your
database, including creating tables and other users as needed.

Another way to do this is to create a separate installation. For the
server you just need to define PGDATA to point to your new data area
(with proper permissions; 700 for the top-level) and PGPORT as another
port number to avoid colliding with your default installation. For
clients you just need PGPORT and (possibly) PGHOST, but of course you
can also specify those in settings.py.

If you are using postgres, spend a few minutes to get familiar with the
docs. They are quite complete and should be your friend.

> 2) After running syncdb, you can't simply execute a simple insert sql
> like this:
>
> INSERT INTO App_contacttype (contact_type, company_id) VALUES
> ('Buyer', 1),('Seller', 1);

Who is using caps for "App_contacttype" in the first place? If you are
coming from MySQL then you should use the conversion program for that
(it used to be packaged with Postgres and probably still is).

> Since Django creates it with quotes the table becomes case sensitive,
> hence it has to be like this:
>
> INSERT INTO "App_contacttype" (contact_type, company_id) VALUES
> ('Buyer', 1),('Seller', 1);

The SQL standard does not support mixed-case table or field names
without using quotes. That might be a reason to stay away from
mixed-case app names; thanks for discovering that for me ;)

> But the problems seem never to end. Now suddenly the execution of the
> insert script says
>
> ERROR: value too long for type character varying(40)
> SQL state: 22001
>
> In MySQL this was no problem. I don't know, right now I am getting a
> bit of cold feet, maybe I should just stick to MySQL.

Uh, if you have fields too long for the maximum length you defined
wouldn't you want to fix that? Either define the schema with a longer
field or switch to a text type.

> The only reason I was considering postgresql was that some research
> suggested postgresql has much better support for changing Schemas
> along the way than MySQL.

There are (many) other reasons to consider postgresql. Don't give up;
there are just a few new things to pick up and you'll be using an
industrial-strength standards-compliant database which will scale as
well as any and better than most.

hth

- Tom

[Melvyn Sopacua]

On 6-7-2012 17:56, Houmie wrote:

> The only reason I was considering postgresql was that some research
> suggested postgresql has much better support for changing Schemas along the
> way than MySQL.

There are many advantages for Postgresql and some for MySQL. But this
isn't a good reason to change from what you know better. If you want to
decide which database is better for you, you should go through the
documentation, get familiar with the authentication system (which you're
not just yet), how it's separated from authorization and figure out how
to do replication and backups. Once you got the administrative part
figured out, start making some apps. This all takes time and willpower
and doesn't combine with deadlines.
--
Melvyn Sopacua

[houmie]

Thanks everybody for the encouragement.

Just to let you know you convinced me not giving up on postgresql.

It is actually quite nice that rules are tighter, to see right away what might be wrong (e.g. field length etc)

Starting up with postgresql is much harder than MySQL, but then it should be ok I think.

I have one further problem I am experiencing with postgre which I didn't with mySQL is the following:

I can't use double quotes in values, e.g.

INSERT INTO app_country (country_code, country_name) VALUES ("AF", "Afghanistan")

I get the error message: ERROR: column "AF" does not exist

This however works fine:

INSERT INTO app_country (country_code, country_name) VALUES ('AF', 'Afghanistan')


This however gives me now a big headache with


Cote D'ivoire

The quote there breaks the string in my INSERT statement.

Any idea how to solve this?

Many Thanks,
Houman

[m1chael]

Hi!

I personally find PostgreSQL much easier to work with. (and the docs
are beautiful)

MySQL has ugly docs, weird syntax, and it does unexpected strange
things. (and their terms of use is a little weird I think too.. yes
MySQL has burned me years ago and i've never forgiven it.)

-endofrant-

~Mike
On Sat, Jul 7, 2012 at 12:21 AM, Dennis Lee Bieber
<wlf...@ix.netcom.com> wrote:
> On Sat, 07 Jul 2012 00:34:18 +0100, houmie <hou...@gmail.com> declaimed
> the following in gmane.comp.python.django.user:

>
>> I can't use double quotes in values, e.g.
>>
>> |INSERT INTO app_country (country_code, country_name) VALUES ("AF",
>> "Afghanistan")|
>>

> In standard SQL, double quotes delimit schema entities (table/column
> names), and single quotes delimit strings. MySQL's relaxed parser
> allowed double quotes for strings -- unless...
>
> -=-=-=-
> If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted
> only within single quotation marks because a string quoted within double
> quotation marks is interpreted as an identifier.
> -=-=-=-

>
>>
>> |INSERT INTO app_country (country_code, country_name) VALUES ('AF',
>> 'Afghanistan')
>> |
>>
>> |This however gives me now a big headache with
>> |
>>
>> |Cote D'ivoire
>>
>> The quote there breaks the string in my INSERT statement.
>>
>> Any idea how to solve this?
>>

> Don't hand-code the SQL yourself -- use the DB-API adapter (since
> you state SQL problem you are bypassing the Django ORM system) with
> parameterized queries and let IT handle any escaping needed for such
> characters.
>
> SQL = "insert into app_country (country_code, coutnry_name) values
> (?, ?)"
> {You'll need to check your adapter for the correct placeholder: SQLite3
> uses ?, MySQL uses %s, PostgreSQL use <what>}
>
> Then supply the values as a tuple to the execute command.
> cursor.execute(SQL, ("cd", "Cote D'ivoire") )
> --
> Wulfraed Dennis Lee Bieber AF6VN
> wlf...@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...@googlegroups.com.
> To unsubscribe from this group, send email to django-users...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
>

[houmie]

Oh one more thing:

I have created a new superuser for postgre called django_user

Now when I try to connect to it it says:

FATAL: Peer authentication failed for user "django_user"

But the password is correct, I had no choice than change the settings
in /etc/postgresql/9.1/main/pg_hba.conf

from

# "local" is for Unix domain socket connections only
local all all peer

to

# "local" is for Unix domain socket connections only
local all all trust


Is this the normal approach? Do I have to do the same thing on my
production box?

Many Thanks,

[kenneth gonsalves]

On Sat, 2012-07-07 at 12:34 +0100, houmie wrote:
> # "local" is for Unix domain socket connections only
> local all all peer
>
> to
>
> # "local" is for Unix domain socket connections only
> local all all trust
>
>
> Is this the normal approach? Do I have to do the same thing on my
> production box?

instead of 'trust' use password
--
regards
Kenneth Gonsalves