Search Results

Search found 46 results on 2 pages for 'psycopg2'.

Page 2/2 | < Previous Page | 1 2 

• Convert object to DateRange

  - by user655832

  I'm querying an underlying PostgreSQL database using Pandas 0.8. Pandas is returning the DataFrame properly but the underlying timestamp column in my database is being returned as a generic "object" type in Pandas. As I would eventually like to seasonal normalization of my data I am curious as to how to convert this generic "object" column to something that is appropriate for analysis. Here is my current code to retrieve the data: # get records from db example import pandas.io.sql as psql import psycopg2 # define query to get all subs created this year QRY = """ select i i, i * random() f, case when random() > 0.5 then true else false end t, (current_date - (i*random())::int)::timestamp with time zone tsz from generate_series(1,1000) as s(i) order by 4 ; """ CONN_STRING = "host='localhost' port=5432 dbname='postgres' user='postgres'" # connect to db conn = psycopg2.connect(CONN_STRING) # get some data set index on relid column df = psql.frame_query(QRY, con=conn) print "Row count retrieved: %i" % (len(df),) Thanks for any help you can render. M

  Read the article

• How to use database adapters' cursors safely?

  - by lvictorino

  I started to use psycopg2 to connect my little python script to a PostgreSQL database few days ago. After some research I found that a lot of database connector, like psycopg, work using cursors. I know what is a cursor and how to use it. But I still wonder if it's safe to use the same cursor all along the script life. Is it safe? Or would it be preferable to use a different cursor for each query?

  Read the article

• Install python-psycopg on Ubuntu 9.10

  - by jack

  How can I install python-psycopg (not python-psycopg2) on Ubuntu 9.10 "apt-get install python-psycopg" returns "Package python-psycopg has no installation candidate" I also downloaded source code at psycopg-1.1.21.tar.gz but didn't found "make" command in the archive.

  Read the article

• Why would I get a duplicate key error when updating a row?

  - by hdx

  I'm using postgres and I'm getting the duplicate key error when updating a row: cursor.execute("UPDATE jiveuser SET userenabled = 0 WHERE userid = %s" % str(userId)) psycopg2.IntegrityError: duplicate key value violates unique constraint "jiveuser_pk" I don't understand how updating a row can cause this error... any help will be much appreciated.

  Read the article

• Postgresql fails to start on Ubuntu 10.04.4 LTS

  - by cancerballs

  I installed postgresql 9.2 from add-apt-repository ppa:pitti/postgresql using apt-get install postgresql-9.2 At the end of the install and every time I try to launch postgresql by using the following command /etc/init.d/postgresql start or service postgresql start I get this error: Error: could not exec /usr/lib/postgresql/9.2/bin/pg_ctl /usr/lib/postgresql/9.2/bin/pg_ctl start -D /var/lib/postgresql/9.2/main -l /var/log/postgresql/postgresql-9.2-main.log -s -o -c config_file="/etc/postgresql/9.2/main/postgresql.conf" : [fail] invoke-rc.d: initscript postgresql, action "start" failed. dpkg: error processing postgresql-9.2 (--configure): subprocess installed post-installation script returned error exit status 1 Errors were encountered while processing: postgresql-9.2 E: Sub-process /usr/bin/dpkg returned an error code (1) I have tried everything found here: How to thoroughly purge and reinstall postgresql on ubuntu and here: Eliminating non working postgresql installations on ubuntu 10-04 and starting af. I have also done dpkg -P --force-remove-reinstreq postgresql-client-9.2 in my attempt to remove everything postgres related from my server. After removing postgresql I have used dpkg --get-selections | grep postg To be sure there is nothing left and I can do a clean install. I have also made sure that the files and folders mentioned in the error message have the right permissions. The /var/log/postgresql/postgresql-9.2-main.log file is empty. I have tried installing every postgresql version from 8.3 to 9.2 and I get the same error on every time. I once managed to compile postgresql from the source provided on their website but then I encountered weird errors with psycopg2 so I figured I'd install postgresql this way and avoid those errors. Also when I type apt-get install postgresql it by default tries to install the 8.3 version even when I can find the package by typing apt-get install postgresql-9.2.

  Read the article

• Django ORM and PostgreSQL connection limits

  - by bennylope

  I'm running a Django project on Postgresql 8.1.21 (using Django 1.1.1, Python2.5, psycopg2, Apache2 with mod_wsgi 3.2). We've recently encountered this lovely error: OperationalError: FATAL: connection limit exceeded for non-superusers I'm not the first person to run up against this. There's a lot of discussion about this error, specifically with psycopg, but much of it centers on older versions of Django and/or offer solutions involving edits to code in Django itself. I've yet to find a succinct explanation of how to solve the problem of the Django ORM (or psycopg, whichever is really responsible, in this case) leaving open Postgre connections. Will simply adding connection.close() at the end of every view solve this problem? Better yet, has anyone conclusively solved this problem and kicked this error's ass?

  Read the article

• How to build an interactive search engine web interface using python

  - by asmaier

  I have build a static web interface for searching data from some tables in my PostgreSQL database. The query website consists of a simple textfield for entering the search term, the result website presents the results as a simple html table. The server side code for searching the PostgreSQL database and returning the results is written in python using psycopg2. Now I would like to add some interactive "Ajax features" to my search engine. When entering the search term I would like to be able to see a list of possible search terms like Google does it. On the results page, I would like to be able to sort the table showing the results. What would be the easiest/recommended way to implement these features for my search engine web site? Do I need a full-fledged web framework like Django for that?

  Read the article

• Python + PostgreSQL + strange ascii = UTF8 encoding error

  - by Claudiu

  I have ascii strings which contain the character "\x80" to represent the euro symbol: >>> print "\x80" € When inserting string data containing this character into my database, I get: psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0x80 HINT: This error can also happen if the byte sequence does not match the encodi ng expected by the server, which is controlled by "client_encoding". I'm a unicode newbie. How can I convert my strings containing "\x80" to valid UTF-8 containing that same euro symbol? I've tried calling .encode and .decode on various strings, but run into errors: >>> "\x80".encode("utf-8") Traceback (most recent call last): File "<pyshell#14>", line 1, in <module> "\x80".encode("utf-8") UnicodeDecodeError: 'ascii' codec can't decode byte 0x80 in position 0: ordinal not in range(128)

  Read the article

• SQL get data out of BEGIN; ...; END; block in python

  - by Claudiu

  I want to run many select queries at once by putting them between BEGIN; END;. I tried the following: cur = connection.cursor() cur.execute(""" BEGIN; SELECT ...; END;""") res = cur.fetchall() However, I get the error: psycopg2.ProgrammingError: no results to fetch How can I actually get data this way? Likewise, if I just have many selects in a row, I only get data back from the latest one. Is there a way to get data out of all of them?

  Read the article

• How can I reshape and aggregate list of tuples in Python?

  - by radek

  I'm a newb to Python so apologies in advance if my question looks trivial. From a psycopg2 query i have a result in the form of a list of tuples looking like: [(1, 0), (1, 0), (1, 1), (2, 1), (2, 2), (2, 2), (2, 2)] Each tuple represents id of a location where event happened and hour of the day when event took place. I'd like to reshape and aggregate this list with subtotals for each hour in each location, to a form where it looks like: [(1, 0, 2), (1, 1, 1), (1, 2, 0), (2, 0, 0), (2, 1, 1), (2, 3, 3)] Where each touple will now tell me that, for example: in location 1, at hour 0 there were 2 events; in location 1, at hour 1 there was 1 event; and so on... If there were 0 events at certain hour, I still would like to see it, as for example 0 events at 0 hours in location 2: (2, 0, 0) How could I implement it in Python?

  Read the article

• PostgreSQL lots of large Arrays and Writes

  - by strife911

  Hi, I am running a python program that spawns 8 threads and as each thread launch its own postmaster process via psycopg2. This is to maximize the use of my CPU-cores (8). Each thread call a series of SQL Functions. Most of these functions go through many thousands of rows each associated to a large FLOAT8[] Array (250-300) values by using unnest() and multiplying each FLOAT8 by an another FLOAT8 associated to each row. This Array approach minimized the size of the Indexes and the Tables. The Function ends with an Insert into another Table of a row of the same form (pk INT4, array FLOAT8[]). Some SQL Functions called by python will Update a row of these kind of Tables (with large Arrays). Now I currently have configured PostgreSQL to use most of the memory for cache (effective_cache_size of 57 GB I think) and only a small amount of it for shared memory (1GB I think). First, I was wondering what the difference between Cache and Shared memory was in regards to PostgreSQL (and my application). What I have noticed is that only about 20-40% of my total CPU processing power is used during the most Read intensive parts of the application (Select unnest(array) etc). So secondly, I was wondering what I could do to improve this so that 100% of the CPU is used. Based on my observations, it does not seem to have anything to do with python or its GIL. Thanks

  Read the article

• 2-step user registration with Django

  - by David S

  I'm creating a website with Django and want a fairly common 2-step user registration. What I mean by this is that the user fills in the some basic user information + some application specific information (sort of like a coupon value). Upon submit, an email is sent to ensure email address is valid. This email should contain a link to click on to "finish" the registration. When the link is clicked, the user is marked as validated and they are directed to a new page to complete optional "user profile" type information. So, pretty basic stuff. I have done some research and found django-registration by James Bennett. I do know who James is and have seen him at PyCons and DjanoCons in the past. There is obviously very few people in the world that know Django better than James (so, I know the quality of the code/app is good). But, it almost seems like a bit of over kill. I've read through the docs and was a bit confused (maybe I'm just being a bit dense today). I believe that if I do use django-registration, I will need to have some custom forms, etc. Is there anything else out there I should evaluate? Or are there any good tutorials or videos on using django-registration? I've done a bit of googling, but haven't found anything. But, I suspect that it might be a case of a lot of very common words that don't really find what you are looking for (django user registration tutorial/example). Or is just a case where it would be just about as easy to build your own solution with Django forms, etc? Here is the tech stack I'm using: Python 2.7.2 Django 1.3.1 PostgreSQL 9.1 psycopg2 2.4.1 Twitter Bootstrap 2.0.2

  Read the article

• virtualenv macosX --no-site-package ignored

  - by Tristram Gräbener

  Hello, I'm having problems with macOSX and virtualenv. It seems to ignore --no-site-package. Using exactly the same commands with linux (archlinux) it works. It it macOSX 10.5 with python 2.5 curl -o virtualenv.py 'http://bitbucket.org/ianb/virtualenv/raw/tip/virtualenv.py Create a new environment python virtualenv.py --no-site-packages foo New python executable in foo/bin/python Installing setuptools...........................done. Activate it source foo/bin/activate Try to install something in it. Despite virtualenv it looks for the system-wide install easy_install cherrypy Searching for cherrypy Best match: CherryPy 3.1.2 Adding CherryPy 3.1.2 to easy-install.pth file Using /Library/Python/2.5/site-packages Processing dependencies for cherrypy Finished processing dependencies for cherrypy Yet it doesn't find the module (foo)guidage-multimodal:~ tristram$ python Python 2.5.1 (r251:54863, Feb 6 2009, 19:02:12) [GCC 4.0.1 (Apple Inc. build 5465)] on darwin Type "help", "copyright", "credits" or "license" for more information. >>> import cherrypy Traceback (most recent call last): File "<stdin>", line 1, in <module> ImportError: No module named cherrypy I tried PIP after looking at http://stackoverflow.com/questions/1382925/virtualenv-no-site-packages-and-pip-still-finding-global-packages However it fails installing psycopg2 (some problems with gcc). Also I would like to be able to have a setup.py (from distribute) that does the whole woork

  Read the article

• Can't install egenix-mx-base on Django production VPS

  - by Shane

  I have been following these instructions for setting up a Django production server with postgres, apache, nginx, and memcache. My problem is that I cannot get egenix-mx-base to install and without this I cannot get psycopg2 to work and therefore no database access :(. I am attempting this on a VPS running a clean install of Ubuntu Hardy (8.04) and have followed all instructions on the site to a T. The error message is as follows: $ easy_install egenix-mx-base Searching for egenix-mx-base Reading http://pypi.python.org/simple/egenix-mx-base/ Reading http://www.egenix.com/products/python/mxBase/ Reading http://www.lemburg.com/python/mxExtensions.html Reading http://www.egenix.com/ Best match: egenix-mx-base 3.1.3 Downloading http://downloads.egenix.com/python/egenix-mx-base-3.1.3.tar.gz Processing egenix-mx-base-3.1.3.tar.gz Running egenix-mx-base-3.1.3/setup.py -q bdist_egg --dist-dir /tmp/easy_install-iF7qzl/egenix-mx-base-3.1.3/egg-dist-tmp-laxvcS Warning: Can't read registry to find the necessary compiler setting Make sure that Python modules _winreg, win32api or win32con are installed. In file included from mx/TextTools/mxTextTools/mxte.c:42: mx/TextTools/mxTextTools/mxte_impl.h: In function ‘mxTextTools_TaggingEngine’: mx/TextTools/mxTextTools/mxte_impl.h:345: warning: pointer targets in initialization differ in signedness mx/TextTools/mxTextTools/mxte_impl.h:364: warning: pointer targets in initialization differ in signedness mx/URL/mxURL/mxURL.c: In function ‘mxURL_SetFromString’: mx/URL/mxURL/mxURL.c:676: warning: pointer targets in initialization differ in signedness mx/UID/mxUID/mxUID.c: In function ‘mxUID_Verify’: mx/UID/mxUID/mxUID.c:333: warning: pointer targets in passing argument 1 of ‘sscanf’ differ in signedness mx/UID/mxUID/mxUID.c: In function ‘mxUID_New’: mx/UID/mxUID/mxUID.c:462: warning: pointer targets in passing argument 1 of ‘mxUID_CRC16’ differ in signedness error: Setup script exited with error: build/bdist.linux-x86_64-py2.5_ucs4/dumb/egenix_mx_base-3.1.3-py2.5.egg-info: Is a directory Thank you to anyone who takes the time to try to help me.

  Read the article

• Database for Python Twisted

  - by Will

  There's an API for Twisted apps to talk to a database in a scalable way: twisted.enterprise.dbapi The confusing thing is, which database to pick? The database will have a Twisted app that is mostly making inserts and updates and relatively few selects, and then other strictly-read-only clients that are accessing the database directly making selects. (The read-only users are not necessarily selecting the data that the Twisted app is inserting; its not as though the database is being used as a message-queue) My understanding - which I'd like corrected/adviced - is that: Postgres is a great DB, but all the Python bindings - and there is a confusing maze of them - are abandonware There is psycopg2, but that makes a lot of noise about doing its own connection-pooling and things; does this co-exist gracefully/usefully/transparently with the Twisted async database connection pooling and such? SQLLite is a great database for little things but if used in a multi-user way it does whole-database locking, so performance would suck in the usage pattern I envisage MySQL - after the Oracle takeover, who'd want to adopt it now or adopt a fork? Is there anything else out there?

  Read the article

• PostgreSQL, Foreign Keys, Insert speed & Django

  - by Miles

  A few days ago, I ran into an unexpected performance problem with a pretty standard Django setup. For an upcoming feature, we have to regenerate a table hourly, containing about 100k rows of data, 9M on the disk, 10M indexes according to pgAdmin. The problem is that inserting them by whatever method literally takes ages, up to 3 minutes of 100% disk busy time. That's not something you want on a production site. It doesn't matter if the inserts were in a transaction, issued via plain insert, multi-row insert, COPY FROM or even INSERT INTO t1 SELECT * FROM t2. After noticing this isn't Django's fault, I followed a trial and error route, and hey, the problem disappeared after dropping all foreign keys! Instead of 3 minutes, the INSERT INTO SELECT FROM took less than a second to execute, which isn't too surprising for a table <= 20M on the disk. What is weird is that PostgreSQL manages to slow down inserts by 180x just by using 3 foreign keys. Oh, disk activity was pure writing, as everything is cached in RAM; only writes go to the disks. It looks like PostgreSQL is working very hard to touch every row in the referred tables, as 3MB/sec * 180s is way more data than the 20MB this new table takes on disk. No WAL for the 180s case, I was testing in psql directly, in Django, add ~50% overhead for WAL logging. Tried @commit_on_success, same slowness, I had even implemented multi row insert and COPY FROM with psycopg2. That's another weird thing, how can 10M worth of inserts generate 10x 16M log segments? Table layout: id serial primary, a bunch of int32, 3 foreign keys to small table, 198 rows, 16k on disk large table, 1.2M rows, 59 data + 89 index MB on disk large table, 2.2M rows, 198 + 210MB So, am I doomed to either drop the foreign keys manually or use the table in a very un-Django way by defining saving bla_id x3 and skip using models.ForeignKey? I'd love to hear about some magical antidote / pg setting to fix this.

  Read the article

• django + south + python: strange behavior when using a text string received as a parameter in a func

  - by carlosescri

  Hello, this is my first question. I'm trying to execute a SQL query in django (south migration): from django.db import connection # ... class Migration(SchemaMigration): # ... def transform_id_to_pk(self, table): try: db.delete_primary_key(table) except: pass finally: cursor = connection.cursor() # This does not work cursor.execute('SELECT MAX("id") FROM "%s"', [table]) # I don't know if this works. try: minvalue = cursor.fetchone()[0] except: minvalue = 1 seq_name = table + '_id_seq' db.execute('CREATE SEQUENCE "%s" START WITH %s OWNED BY "%s"."id"', [seq_name, minvalue, table]) db.execute('ALTER TABLE "%s" ALTER COLUMN id SET DEFAULT nextval("%s")', [table, seq_name + '::regclass']) db.create_primary_key(table, ['id']) # ... I use this function like this: self.transform_id_to_pk('my_table_name') So it should: Find the biggest existent ID or 0 (it crashes) Create a sequence name Create the sequence Update the ID field to use sequence Update the ID as PK But it crashes and the error says: File "../apps/accounting/migrations/0003_setup_tables.py", line 45, in forwards self.delegation_table_setup(orm) File "../apps/accounting/migrations/0003_setup_tables.py", line 478, in delegation_table_setup self.transform_id_to_pk('accounting_delegation') File "../apps/accounting/migrations/0003_setup_tables.py", line 20, in transform_id_to_pk cursor.execute(u'SELECT MAX("id") FROM "%s"', [table.encode('utf-8')]) File "/Library/Python/2.6/site-packages/django/db/backends/util.py", line 19, in execute return self.cursor.execute(sql, params) psycopg2.ProgrammingError: relation "E'accounting_delegation'" does not exist LINE 1: SELECT MAX("id") FROM "E'accounting_delegation'" ^ I have shortened the file paths for convenience. What does that "E'accounting_delegation'" mean? How could I get rid of it? Thank you! Carlos.

  Read the article

• Django + gunicorn + virtualenv + Supervisord issue

  - by Florian Le Goff

  Dear all, I have a strange issue with my virtualenv + gunicorn setup, only when gunicorn is launched via supervisord. I do realize that it may very well be an issue with my supervisord and I would appreciate any feedback on a better place to ask for help... In a nutshell : when I run gunicorn from my user shell, inside my virtualenv, everything is working flawlessly. I'm able to access all the views of my Django project. When gunicorn is launched by supervisord at the system startup, everything is OK. But, if I have to kill the gunicorn_django processes, or if I perform a supervisord restart, once that gunicorn_django has relaunched, every request is answered with a weird Traceback : (...) File "/home/hc/prod/venv/lib/python2.6/site-packages/Django-1.2.5-py2.6.egg/django/db/__init__.py", line 77, in connection = connections[DEFAULT_DB_ALIAS] File "/home/hc/prod/venv/lib/python2.6/site-packages/Django-1.2.5-py2.6.egg/django/db/utils.py", line 92, in __getitem__ backend = load_backend(db['ENGINE']) File "/home/hc/prod/venv/lib/python2.6/site-packages/Django-1.2.5-py2.6.egg/django/db/utils.py", line 50, in load_backend raise ImproperlyConfigured(error_msg) TemplateSyntaxError: Caught ImproperlyConfigured while rendering: 'django.db.backends.postgresql_psycopg2' isn't an available database backend. Try using django.db.backends.XXX, where XXX is one of: 'dummy', 'mysql', 'oracle', 'postgresql', 'postgresql_psycopg2', 'sqlite3' Error was: cannot import name utils Full stack available here : http://pastebin.com/BJ5tNQ2N I'm running... Ubuntu/maverick (up-to-date) Python = 2.6.6 virtualenv = 1.5.1 gunicorn = 0.12.0 Django = 1.2.5 psycopg2 = '2.4-beta2 (dt dec pq3 ext)' gunicorn configuration : backlog = 2048 bind = "127.0.0.1:8000" pidfile = "/tmp/gunicorn-hc.pid" daemon = True debug = True workers = 3 logfile = "/home/hc/prod/log/gunicorn.log" loglevel = "info" supervisord configuration : [program:gunicorn] directory=/home/hc/prod/hc command=/home/hc/prod/venv/bin/gunicorn_django -c /home/hc/prod/hc/gunicorn.conf.py user=hc umask=022 autostart=True autorestart=True redirect_stderr=True Any advice ? I've been stuck on this one for quite a while. It seems like some weird memory limit, as I'm not enforcing anything special : $ ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 20 file size (blocks, -f) unlimited pending signals (-i) 16382 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) unlimited virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Thank you.

  Read the article

• A simple Python deployment problem - a whole world of pain

  - by Evgeny

  We have several Python 2.6 applications running on Linux. Some of them are Pylons web applications, others are simply long-running processes that we run from the command line using nohup. We're also using virtualenv, both in development and in production. What is the best way to deploy these applications to a production server? In development we simply get the source tree into any directory, set up a virtualenv and run - easy enough. We could do the same in production and perhaps that really is the most practical solution, but it just feels a bit wrong to run svn update in production. We've also tried fab, but it just never works first time. For every application something else goes wrong. It strikes me that the whole process is just too hard, given that what we're trying to achieve is fundamentally very simple. Here's what we want from a deployment process. We should be able to run one simple command to deploy an updated version of an application. (If the initial deployment involves a bit of extra complexity that's fine.) When we run this command it should copy certain files, either out of a Subversion repository or out of a local working copy, to a specified "environment" on the server, which probably means a different virtualenv. We have both staging and production version of the applications on the same server, so they need to somehow be kept separate. If it installs into site-packages, that's fine too, as long as it works. We have some configuration files on the server that should be preserved (ie. not overwritten or deleted by the deployment process). Some of these applications import modules from other applications, so they need to be able to reference each other as packages somehow. This is the part we've had the most trouble with! I don't care whether it works via relative imports, site-packages or whatever, as long as it works reliably in both development and production. Ideally the deployment process should automatically install external packages that our applications depend on (eg. psycopg2). That's really it! How hard can it be?

  Read the article

• Django - I got TemplateSyntaxError when I try open the admin page. (http://DOMAIN_NAME/admin)

  - by user140827

  I use grappelly plugin. When I try open the admin page (/admin) I got TemplateSyntaxError. It says 'get_generic_relation_list' is invalid block tag. TemplateSyntaxError at /admin/ Invalid block tag: 'get_generic_relation_list', expected 'endblock' Request Method: GET Request URL: http://DOMAIN_NAME/admin/ Django Version: 1.4 Exception Type: TemplateSyntaxError Exception Value: Invalid block tag: 'get_generic_relation_list', expected 'endblock' Exception Location: /opt/python27/django/1.4/lib/python2.7/site-packages/django/template/base.py in invalid_block_tag, line 320 Python Executable: /opt/python27/django/1.4/bin/python Python Version: 2.7.0 Python Path: ['/home/vhosts/DOMAIN_NAME/httpdocs/media', '/home/vhosts/DOMAIN_NAME/private/new_malinnikov/lib', '/home/vhosts/DOMAIN_NAME/httpdocs/', '/home/vhosts/DOMAIN_NAME/private/new_malinnikov', '/home/vhosts/DOMAIN_NAME/private/new_malinnikov', '/home/vhosts/DOMAIN_NAME/private', '/opt/python27/django/1.4', '/home/vhosts/DOMAIN_NAME/httpdocs', '/opt/python27/django/1.4/lib/python2.7/site-packages/setuptools-0.6c12dev_r88846-py2.7.egg', '/opt/python27/django/1.4/lib/python2.7/site-packages/pip-0.8.1-py2.7.egg', '/opt/python27/django/1.4/lib/python27.zip', '/opt/python27/django/1.4/lib/python2.7', '/opt/python27/django/1.4/lib/python2.7/plat-linux2', '/opt/python27/django/1.4/lib/python2.7/lib-tk', '/opt/python27/django/1.4/lib/python2.7/lib-old', '/opt/python27/django/1.4/lib/python2.7/lib-dynload', '/opt/python27/lib/python2.7', '/opt/python27/lib/python2.7/plat-linux2', '/opt/python27/lib/python2.7/lib-tk', '/opt/python27/django/1.4/lib/python2.7/site-packages', '/opt/python27/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg', '/opt/python27/lib/python2.7/site-packages/flup-1.0.3.dev_20100525-py2.7.egg', '/opt/python27/lib/python2.7/site-packages/virtualenv-1.5.1-py2.7.egg', '/opt/python27/lib/python2.7/site-packages/SQLAlchemy-0.6.4-py2.7.egg', '/opt/python27/lib/python2.7/site-packages/SQLObject-0.14.1-py2.7.egg', '/opt/python27/lib/python2.7/site-packages/FormEncode-1.2.3dev-py2.7.egg', '/opt/python27/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-linux-x86_64.egg', '/opt/python27/lib/python2.7/site-packages/psycopg2-2.2.2-py2.7-linux-x86_64.egg', '/opt/python27/lib/python2.7/site-packages/pysqlite-2.6.0-py2.7-linux-x86_64.egg', '/opt/python27/lib/python2.7/site-packages', '/opt/python27/lib/python2.7/site-packages/PIL'] Server time: ???, 7 ??? 2012 04:19:42 +0700 Error during template rendering In template /home/vhosts/DOMAIN_NAME/httpdocs/templates/grappelli/admin/base.html, error at line 28 Invalid block tag: 'get_generic_relation_list', expected 'endblock' 18 <!--[if lt IE 8]> 19 <script src="http://ie7-js.googlecode.com/svn/version/2.0(beta3)/IE8.js" type="text/javascript"></script> 20 <![endif]--> 21 {% block javascripts %} 22 <script type="text/javascript" src="{% admin_media_prefix %}jquery/jquery-1.3.2.min.js"></script> 23 <script type="text/javascript" src="{% admin_media_prefix %}js/admin/Bookmarks.js"></script> 24 <script type="text/javascript"> 25 // Admin URL 26 var ADMIN_URL = "{% get_admin_url %}"; 27 // Generic Relations 28 {% get_generic_relation_list %} 29 // Get Bookmarks 30 $(document).ready(function(){ 31 $.ajax({ 32 type: "GET", 33 url: '{% url grp_bookmark_get %}', 34 data: "path=" + escape(window.location.pathname + window.location.search), 35 dataType: "html", 36 success: function(data){ 37 $('ul#bookmarks').replaceWith(data); 38 }

  Read the article

• Postgresql count+sort performance

  - by invictus

  I have built a small inventory system using postgresql and psycopg2. Everything works great, except, when I want to create aggregated summaries/reports of the content, I get really bad performance due to count()'ing and sorting. The DB schema is as follows: CREATE TABLE hosts ( id SERIAL PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE items ( id SERIAL PRIMARY KEY, description TEXT ); CREATE TABLE host_item ( id SERIAL PRIMARY KEY, host INTEGER REFERENCES hosts(id) ON DELETE CASCADE ON UPDATE CASCADE, item INTEGER REFERENCES items(id) ON DELETE CASCADE ON UPDATE CASCADE ); There are some other fields as well, but those are not relevant. I want to extract 2 different reports: - List of all hosts with the number of items per, ordered from highest to lowest count - List of all items with the number of hosts per, ordered from highest to lowest count I have used 2 queries for the purpose: Items with host count: SELECT i.id, i.description, COUNT(hi.id) AS count FROM items AS i LEFT JOIN host_item AS hi ON (i.id=hi.item) GROUP BY i.id ORDER BY count DESC LIMIT 10; Hosts with item count: SELECT h.id, h.name, COUNT(hi.id) AS count FROM hosts AS h LEFT JOIN host_item AS hi ON (h.id=hi.host) GROUP BY h.id ORDER BY count DESC LIMIT 10; Problem is: the queries runs for 5-6 seconds before returning any data. As this is a web based application, 6 seconds are just not acceptable. The database is heavily populated with approximately 50k hosts, 1000 items and 400 000 host/items relations, and will likely increase significantly when (or perhaps if) the application will be used. After playing around, I found that by removing the "ORDER BY count DESC" part, both queries would execute instantly without any delay whatsoever (less than 20ms to finish the queries). Is there any way I can optimize these queries so that I can get the result sorted without the delay? I was trying different indexes, but seeing as the count is computed it is possible to utilize an index for this. I have read that count()'ing in postgresql is slow, but its the sorting that are causing me problems... My current workaround is to run the queries above as an hourly job, putting the result into a new table with an index on the count column for quick lookup. I use Postgresql 9.2.

  Read the article

< Previous Page | 1 2