PostgresSQL or MySql with django?

[frocco]

Hello,

What is the recommended database for django?

I have used MySQL, but am interested in Postgres.

Thanks

[Matthias Müller]

Make a +1 for postgres. I like the licensemodel of PG more than the license model of Mysql. There's a big community behind (no big company) and there is only one version. Mysql confuses me with the different versions (MariaDB, MySQL..).

Since PG has the Hot-Stream-Feature it became more and more used in productive applications.

My 2 cents.

Matthias

2013/3/18 frocco <far...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Philip Goh]

They're much of a muchness. I've favoured Postgres because it seems more straightforward as there aren't multiple distributions and crucially multiple storage engines to worry about. In MySQL's favour, Amazon RDS makes it incredibly simple to set up a scalable and reliable database so bear that in mind if you're targeting Amazon's EC2 infrastructure.

In the end, if you're asking this question you can't go far wrong if you toss a coin and pick one.

Kind regards,

Phil

[Shawn Milochik]

Postgres.

Just from conversations I've seen in the community, MySQL has a
thousand edge-cases which cause problems. From previous conversations
on thsi topic, I think more people use Postgres.

[frocco]

Thank you for your feedback.
Other than the docs, is there a good ebook to learn the basics. I just purchased navicat to use.

[Lee Hinde]

On Mar 18, 2013, at 9:34 AM, frocco <far...@gmail.com> wrote:

> Thank you for your feedback.
> Other than the docs, is there a good ebook to learn the basics. I just purchased navicat to use.
>

https://django.2scoops.org/

[Chris Streeter]

I'd add to this that if you are interested in either running with an async worker like gevent or with Python 3 support, MySQL is a no-go. The main MySQL connector, MySQLdb, doesn't yet support Python 3; work is forthcoming in a branch, though no timeline is given. And if you want to have some sort of async worker with gunicorn, there isn't yet a way to do it with MySQL.

- Chris

[frocco]

Thanks, I purchased this book, 

I was looking for postgresSQL tutorial

[Russell Keith-Magee]

On Tue, Mar 19, 2013 at 12:07 AM, Philip Goh <philip...@gmail.com> wrote:

They're much of a muchness. I've favoured Postgres because it seems more straightforward as there aren't multiple distributions and crucially multiple storage engines to worry about. In MySQL's favour, Amazon RDS makes it incredibly simple to set up a scalable and reliable database so bear that in mind if you're targeting Amazon's EC2 infrastructure.

In the end, if you're asking this question you can't go far wrong if you toss a coin and pick one.

Much of a muchness? They *really* aren't. One of them is an actual database. One of them… isn't.

I've used both MySQL and PostgreSQL in production sites, and I've been a developer on Django's ORM for over 7 years. In all that time, my experience of MySQL has been a constant stream of "What the hell were they smoking". In all that time, I can't think of a single thing PostgreSQL has done which was equally surprising. PostgreSQL just does what a database should do.

PostgreSQL is a fast, well engineered database that follows the SQL specification well. It has a rich indexing structure, and a query planner that makes generally smart decisions.

MySQL doesn't implement transactions at all under MyISAM, and gets them *wrong* under InnoDB (constraint deferral anyone?). It has an incredibly naive indexing structure, and a query planner that is dumb as a bag of hammers. If it's even *possible* for the MySQL query planner to use an index, it will almost universally select the *wrong* index. And I hope you weren't planning to do anything exotic like a subquery.

And then, just for giggles, MySQL does stuff like this:

https://code.djangoproject.com/ticket/16809

Summary - if you issue a SELECT query that contains an "WHERE X IS NONE" clause, and nothing matches the SELECT, and an object was inserted by the last statement, the SELECT query returns *the primary key of the object that was recently inserted*, not an empty result set. Anyone who can rationalize this behavior without resorting to medicinal grade hallucinogens wins a shiny new penny. 

Once upon a time, you *might* have been able to argue that MySQL was easier to install, was faster for certain usage profiles, and had better support for master/slave setups. But that was 5-10 years ago. 

At this point in time, the *only* advantage that MySQL has is it's ubiquity, especially when it comes to deployment platforms -- MySQL's historical popularity means that some cloud hosting providers (like Amazon) provide MySQL support before they provide PostgreSQL support. However, you can get cloud hosted PostgreSQL (e.g., Heroku), and it's not *that* hard to set up yourself if that's compatible with your needs.

 

tl;dr - Friends don't let friends use MySQL.

Yours,

Russ Magee %-)

[Lachlan Musicman]

On 19 March 2013 10:50, Russell Keith-Magee <rus...@keith-magee.com> wrote:
> On Tue, Mar 19, 2013 at 12:07 AM, Philip Goh <philip...@gmail.com>

>> In the end, if you're asking this question you can't go far wrong if you
>> toss a coin and pick one.
>
> Much of a muchness? They *really* aren't. One of them is an actual database.
> One of them… isn't.

I remember my databases lecturer almost chocking on her tongue when
someone mentioned MySQL was a database as people were straggling into
the hall. It really stuck in my head because I'd been using it for
years (and I still do on occasion).

> of MySQL has been a constant stream of "What the hell were they smoking". In

> It has an incredibly
> naive indexing structure, and a query planner that is dumb as a bag of
> hammers.

> And then, just for giggles, MySQL does stuff like this:

> Anyone who can rationalize
> this behavior without resorting to medicinal grade hallucinogens wins a
> shiny new penny.

This is gold.

>
> tl;dr - Friends don't let friends use MySQL.

Passion is a wonderful thing - please don't ever leave it at the door.

L.

--
The new creativity is pointing, not making. Likewise, in the future,
the best writers will be the best information managers.

http://www.theawl.com/2013/02/an-interview-with-avant-garde-poet-kenneth-goldsmith

[Christophe Pettus]

On Mar 18, 2013, at 4:50 PM, Russell Keith-Magee wrote:

> Summary - if you issue a SELECT query that contains an "WHERE X IS NONE" clause, and nothing matches the SELECT, and an object was inserted by the last statement, the SELECT query returns *the primary key of the object that was recently inserted*, not an empty result set. Anyone who can rationalize this behavior without resorting to medicinal grade hallucinogens wins a shiny new penny.

You know what's great? There's lots of code out there that expects this behavior. In fact, there are (sometimes) complaints from people moving from MySQL that PostgreSQL doesn't work that way.

I weep for humanity.
--
-- Christophe Pettus
x...@thebuild.com

[frocco]

Hello,

I know this is not django related, but can someone help me with the syntax to port mysql to postgresql?

I tried the mysqldump command with the postgresql compatibility option, but I still cannot import the data.

Thanks

[Tom Evans]

On Wed, Mar 20, 2013 at 1:54 PM, frocco <far...@gmail.com> wrote:
> Hello,
>
> I know this is not django related, but can someone help me with the syntax
> to port mysql to postgresql?
> I tried the mysqldump command with the postgresql compatibility option, but
> I still cannot import the data.
>
> Thanks
>
>

Django itself has mechanisms for importing and exporting data in a
database independent format:

https://docs.djangoproject.com/en/1.5/ref/django-admin/#django-admin-dumpdata

If you are not dealing with huge amounts of data, this can be the
simplest way forward.

Cheers

Tom

[Patrick Craston]

Alternatively, I've found this tool works very well:

https://pypi.python.org/pypi/py-mysql2pgsql

(only thing to look out for are Django IPAddressFields as they are char(15) fields in MySQL and inet fields in Postgres - py-mysql2pgsql is obviously not aware of this)

HTH

Patrick

[Felipe Prenholato]

I'll bookmark this answer and show to anyone who ask about mysql \m/

Felipe 'chronos' Prenholato.
Linux User nº 405489
Home page: http://devwithpassion.com | http://chronosbox.org/blog
GitHub: http://github.com/chronossc/ | Twitter: http://twitter.com/chronossc

2013/3/18 Russell Keith-Magee <rus...@keith-magee.com>

--

[Jian Chang]

postgres!

do what you like

2013/3/18 frocco <far...@gmail.com>

--

[frocco]

I have not been able to get this to work.

Says no command found.

I must say, porting Mysql data to Postgresql is a big pain on a mac.

I am almost inclined to stay with mysql.

I googled for an easy solution, but could not find anything except dbconvert for 100.00

[frocco]

Has anyone used this?

https://github.com/lanyrd/mysql-postgresql-converter/

I cannot get this working, says cannot find the file

[Patrick Craston]

I have found the py-mysql2pgsql to work without problems.

 

Did you install it using “pip install py-mysql2pgsql” (if you are using a virtualenv make sure you are running it from there or have activated the virtualenv)?

 

No command found suggests there is a problem with your path and it can’t find the script.

[Tom Evans]

On Thu, Mar 21, 2013 at 1:06 PM, frocco <far...@gmail.com> wrote:
> Has anyone used this?
>
> https://github.com/lanyrd/mysql-postgresql-converter/
>
> I cannot get this working, says cannot find the file
>

Why not use dumpdata as I suggested? Add your postgresql database
connection in Django as 'postgres', and do this:

python manage,py syncdb --database postgres
python manage.py dumpdata -a > data.json
python manage.py loaddata --database postgres data.json

Then edit settings.py, change the 'postgres' connection to be named
'defaut' and remove your existing mysql default.

This is much easier than messing around trying to get a script which
knows nothing about your database to convert everything correctly.

Cheers

Tom

[frocco]

Yes, I used pip

I get this error.

py-mysql2pgsql' is not recognized as an internal or external command

[Alan Johnson]

I want to echo the support for Postgres. I find it to be a bit more arcane than MySQL, but even in an extremely DB intensive app, Django's ORM is so slick that I rarely have to muck around in Postgres land. I think it's slightly better supported by Django, if you need to use things like distinct(), and other tiny little differences. Two Scoops of Django, which someone mentioned earlier, has become like a holy book to me when it comes to deployment, and they endorse Postgres as well. Navicat is indispensable for getting out of the ORM and seeing what the hell is going on in your DB, as I'm sure you already know. For another migration method, I'll also throw out the option of djang-extensions dumpscript (http://pythonhosted.org/django-extensions/dumpscript.html), which generates the ORM commands to create your database, and can be used for migration.

On Monday, March 18, 2013 11:30:35 AM UTC-4, frocco wrote:

[frocco]

Hi Alan,

so you are saying I should connect django to mysql first and then generate the script.

Then connect django to postgresql and do the import?

[Lachlan Musicman]

Frocco,

No, he's saying you can have two databases set up at the same time.
You can have X databases (I presume).

Cheers
L.

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>



--

[Shawn Milochik]

On Thu, Mar 21, 2013 at 3:44 PM, Lachlan Musicman <dat...@gmail.com> wrote:
> Frocco,
>
> No, he's saying you can have two databases set up at the same time.
> You can have X databases (I presume).
>
> Cheers
> L.
>
> On 22 March 2013 01:13, frocco <far...@gmail.com> wrote:
>> Hi Alan,
>>
>> so you are saying I should connect django to mysql first and then generate
>> the script.
>> Then connect django to postgresql and do the import?
>>

Actually, I think that's exactly what he meant. If MySQL is working,
do the dumpdata commands. Save the output. Change your settings to use
Postgres. Then run the loaddata commands. At that point, you've
successfully replaced MySQL with Postgres.

[Alan Johnson]

You don't have to have the databases set up at the same time for the django-extensions dumpscript method (nor for the dumpdata method). I haven't used it myself, but as I understand it, the script will contain the data you need.

1. Create the script with existing MySQL running
2. Reconfigure settings for PostgreSQL
3. Run the script
   

I'm not saying it's better than any other method, just throwing out the option.

[frocco]

I Just downloaded a product called RazorSQL

http://www.razorsql.com

This product has conversion from MySql to Postgresql.

Works great.