You probably just need to remove &quot;localhost&quot; from your connection string:

    &#39;postgresql:///db_name&#39;

That tells psycopg2 to use Unix-domain sockets. Your default configuration will use &quot;ident&quot; so you&#39;ll be connecting as the user that runs the script. In the default configuration, &quot;md5&quot; only applies to TCP connections.

URL pattern should be: 

    postgresql://user:password@localhost:5432/database_name
pip install psycopg2      
the user should be postgres or any other user you have created and intend to use     

similarly for mySql it would be:

    mysql://user:pass@localhost:3306/database_name
pip install mysql-python

On your Mac, PostgreSQL was set up for `trust` or `peer` authentication for connections from localhost.

On your Ubuntu box it&#39;s set up for `md5` authentication for connections from localhost.

You&#39;ll want to configure a password, or change the authentication mode. See [`pg_hba.conf`](http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html), and the [Ubuntu guide for PostgreSQL](https://help.ubuntu.com/community/PostgreSQL#fe_sendauth:_no_password_supplied) (there&#39;s a section about this error).


Below worked for me. Your connection to your postgres database requires a password; thus, below is what you should write.. 

    pg_user = &quot;magicmike&quot;
    pg_pwd = &quot;test123&quot;
    pg_port = &quot;5432&quot;
    app.config[&quot;SQLALCHEMY_DATABASE_URI&quot;] = &quot;postgresql://{username}:{password}@localhost:{port}/foodversity_db&quot;.format(username=pg_user, password=pg_pwd, port=pg_port)

First make sure that the database server is connected and then run the command again.Silly, but it worked for me.

For Remote Server 

 -  remote server =&gt; `postgresql://&lt;username&gt;:&lt;password&gt;@&lt;ipaddress&gt;:&lt;port&gt;/&lt;database&gt;`


For Local in configuration use 

 -  local db =&gt; `postgressql:///&lt;database&gt;`

I am trying to upload image through admin page, but it keeps saying: 

    [Errno 13] Permission denied: &#39;/path/to/my/site/media/userfolder/2014/05/26&#39;

the folders  ``userfolder/2014/05/26`` are created dynamically while uploading. 

In Traceback, i found that the error is occuring during this command:

In /usr/lib64/python2.6/os.py Line 157. while calling 

    mkdir(name, mode) 

meaning, it cannot create any folder as it doesnot have the permission to do this

I have OpenSuse as OS in Server. In httpd.conf, i have this: 

    &lt;Directory /path/to/my/site/media&gt;
       Order allow,deny
       Allow from all
    &lt;/Directory&gt;

Do I have to chmod or chown something? 


OSError - Errno 13 Permission denied

In my app I have some async web services. Server accept request, return OK response and start processing request with AsyncTaskExecutor. My question is how to enable request scope here because in this processing I need to get class which is annotated by:

    @Scope(value = WebApplicationContext.SCOPE_REQUEST, proxyMode = ScopedProxyMode.TARGET_CLASS)

Now I get exception:

    org.springframework.beans.factory.BeanCreationException: Error creating bean with name &#39;scopedTarget.requestContextImpl&#39;: Scope &#39;request&#39; is not active for the current thread; consider defining a scoped proxy for this bean if you intend to refer to it from a singleton; nested exception is java.lang.IllegalStateException: No thread-bound request found: Are you referring to request attributes outside of an actual web request, or processing a request outside of the originally receiving thread? If you are actually operating within a web request and still receive this message, your code is probably running outside of DispatcherServlet/DispatcherPortlet: In this case, use RequestContextListener or RequestContextFilter to expose the current request.


because it runs in `SimpleAsyncTaskExecutor` and not in `DispatcherServlet`

my async processing of request


    taskExecutor.execute(new Runnable() {
    
    	@Override
    	public void run() {
    		asyncRequest(request);
    	}
    });

where taskExecutor is:

	&lt;bean id=&quot;taskExecutor&quot; class=&quot;org.springframework.core.task.SimpleAsyncTaskExecutor&quot; /&gt;


How to enable request scope in async task executor

This is probably a silly error but I cannot seem to find a satisfying solution.

When running `db.create_all()`, I got the following error.

    sqlalchemy.exc.OperationalError: (OperationalError) fe_sendauth: no password supplied  None None

My database link is set as 

    &#39;postgresql://localhost/db_name&#39;

This worked fine on my Mac and Heroku, but is not OK on ubuntu (digitalocean). 

Any ideas what I might be doing wrong?


SQLAlchemy no password supplied error

This is probably a silly error but I cannot seem to find a satisfying solution.
When running <code>db.create_all()</code>, I got the following error.
<pre><code class="hljs language-yaml">sqlalchemy.exc.OperationalError: (OperationalError) fe_sendauth: no password supplied None None
</code></pre>
My database link is set as
<pre><code class="hljs language-rust">'postgresql://localhost/db_name'
</code></pre>
This worked fine on my Mac and Heroku, but is not OK on ubuntu (digitalocean).
Any ideas what I might be doing wrong?

I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I&#39;d like to update its data promptly.

Here is what I was thinking about so far:

There is a materialized view `mat_view` which gets its data from tables `table1` and `table2` using some join statement.

Whenever something in `table1` or `table2` changes, I already have a trigger which updates a little configuration table `config` consisting of

    table_name | mat_view_name | need_update
    -----------+---------------+------------
    table1     | mat_view      | TRUE/FALSE
    table2     | mat_view      | TRUE/FALSE

So if anything in `table1` changes (there&#39;s a trigger on UPDATE and on DELETE for every statement), the field `need_update` in the first row is set to `TRUE`.
The same goes for `table2` and the second row.

Obviously, if `need_update` is TRUE, then the materialized view must be refreshed.

**UPDATE**:
Since materialized views do not support rules (as @pozs mentioned in a comment below), I would go one step further. I&#39;d create a dummy view `v_mat_view` with the definition &quot;`SELECT * FROM mat_view`&quot;. When the user does a SELECT on this view, I need to create a rule ON SELECT which does the following:

 - check whether `mat_view` should be updated (`SELECT 1 FROM config WHERE mat_view_name=&#39;mat_view&#39; AND need_update=TRUE`)
 - reset the `need_update` flag with `UPDATE config SET need_update=FALSE where mat_view_name=&#39;mat_view&#39;`
 - `REFRESH MATERIALIZED VIEW mat_view`
 - and at last do the original SELECT statement but with `mat_view` as the target.

**UPDATE2**:
I tried creating the steps above:

Create a function that handles the four points mentioned above:

    CREATE OR REPLACE FUNCTION mat_view_selector()
    RETURNS SETOF mat_view AS $body$
    BEGIN
      -- here is checking whether to refresh the mat_view
      -- then return the select:
      RETURN QUERY SELECT * FROM mat_view;
    END;
    $body$ LANGUAGE plpgsql;
    
Create the view `v_mat_view` which really selects from the function `mat_view_selector`:

    CREATE TABLE v_mat_view AS SELECT * from mat_view LIMIT 1;
    DELETE FROM v_mat_view;
    
    CREATE RULE &quot;_RETURN&quot; AS
        ON SELECT TO v_mat_view
        DO INSTEAD 
            SELECT * FROM mat_view_selector();
        -- this also converts the empty table &#39;v_mat_view&#39; into a view.

The result is unsatisfying:
---------------------------

    # explain analyze select field1 from v_mat_view where field2 = 44;
    QUERY PLAN
    Function Scan on mat_view_selector (cost=0.25..12.75 rows=5 width=4)
    (actual time=15.457..18.048 rows=1 loops=1)
    Filter: (field2 = 44)
    Rows Removed by Filter: 20021
    Total runtime: 31.753 ms

in comparison to selecting from the mat_view itself:

    # explain analyze select field1 from mat_view where field2 = 44;
    QUERY PLAN
    Index Scan using mat_view_field2 on mat_view (cost=0.29..8.30 rows=1 width=4)
      (actual time=0.015..0.016 rows=1 loops=1)
    Index Cond: (field2 = 44)
    Total runtime: 0.036 ms

So essentially it DOES work, but performance might be an issue.

Anyone have better ideas?
If not, then I would have to implement it somehow in the application logic or worse: run a simple cronjob that runs every minute or so.

Refresh a materialized view automatically using a rule or notify

I would like to have PostgreSQL return the result of a query as one JSON array. Given

    create table t (a int primary key, b text);

    insert into t values (1, &#39;value1&#39;);
    insert into t values (2, &#39;value2&#39;);
    insert into t values (3, &#39;value3&#39;);

I would like something similar to

    [{&quot;a&quot;:1,&quot;b&quot;:&quot;value1&quot;},{&quot;a&quot;:2,&quot;b&quot;:&quot;value2&quot;},{&quot;a&quot;:3,&quot;b&quot;:&quot;value3&quot;}]
or 

    {&quot;a&quot;:[1,2,3], &quot;b&quot;:[&quot;value1&quot;,&quot;value2&quot;,&quot;value3&quot;]}

(actually it would be more useful to know both). I have tried some things like

    select row_to_json(row) from (select * from t) row;
    select array_agg(row) from (select * from t) row;
    select array_to_string(array_agg(row), &#39;&#39;) from (select * from t) row;

And I feel I am close, but not there really. Should I be looking at other documentation except for [9.15. JSON Functions and Operators][1]?

By the way, I am not sure about my idea. Is this a usual design decision? My thinking is that I could, of course, take the result (for example) of the first of the above 3 queries and manipulate it slightly in the application before serving it to the client, but if PostgreSQL can create the final JSON object directly, it would be simpler, because I still have not included any dependency on any JSON library in my application. 


  [1]: http://www.postgresql.org/docs/9.3/static/functions-json.html &quot;9.15. JSON Functions and Operators&quot;

PostgreSQL return result set as JSON array?

I have the following schema:

    CREATE TABLE author (
        id   integer
      , name varchar(255)
    );
    CREATE TABLE book (
        id        integer
      , author_id integer
      , title     varchar(255)
      , rating    integer
    );

And I want each author with its last book:

    SELECT book.id, author.id, author.name, book.title as last_book
    FROM author
    JOIN book book ON book.author_id = author.id
    
    GROUP BY author.id
    ORDER BY book.id ASC

Apparently you can do that in mysql: https://stackoverflow.com/questions/6468314/join-two-tables-returning-just-one-row-from-the-second-table-mysql.

But postgres gives this error:

&gt; ERROR: column &quot;book.id&quot; must appear in the GROUP BY clause or be used
&gt; in an aggregate function: SELECT book.id, author.id, author.name,
&gt; book.title as last_book FROM author JOIN book book ON book.author_id =
&gt; author.id GROUP BY author.id ORDER BY book.id ASC

[It&#39;s because][1]:

&gt; When GROUP BY is present, it is not valid for the SELECT list
&gt; expressions to refer to ungrouped columns except within aggregate
&gt; functions, since there would be more than one possible value to return
&gt; for an ungrouped column.

How can I specify to postgres: &quot;Give me only the last row, when ordered by `joined_table.id`, in the joined table ?&quot;

-----------
Edit:
With this data:

    INSERT INTO author (id, name) VALUES
      (1, &#39;Bob&#39;)
    , (2, &#39;David&#39;)
    , (3, &#39;John&#39;);
    
    INSERT INTO book (id, author_id, title, rating) VALUES
      (1, 1, &#39;1st book from bob&#39;, 5)
    , (2, 1, &#39;2nd book from bob&#39;, 6)
    , (3, 1, &#39;3rd book from bob&#39;, 7)
    , (4, 2, &#39;1st book from David&#39;, 6)
    , (5, 2, &#39;2nd book from David&#39;, 6);

I should see:

    book_id author_id name    last_book
    3       1         &quot;Bob&quot;   &quot;3rd book from bob&quot;
    5       2         &quot;David&quot; &quot;2nd book from David&quot;

  [1]: http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-GROUPBY

How to join only one row in joined table with postgres?

Using psycopg2 package with python 2.7 I keep getting the titled error: psycopg2.DatabaseError: SSL SYSCALL error: EOF detected

It only occurs when I add a `WHERE column LIKE &#39;&#39;%X%&#39;&#39;` clause to my pgrouting query. An example:

    SELECT id1 as node, cost FROM PGR_Driving_Distance(
      &#39;SELECT id, source, target, cost 
         FROM edge_table
         WHERE cost IS NOT NULL and column LIKE &#39;&#39;%x%&#39;&#39; &#39;,
      1, 10, false, false)

Threads on the internet suggest it is an issue with SSL intuitively, but whenever I comment out the pattern matching side of things the query and connection to the database works fine.

This is on a local database running Xubuntu 13.10.

After further investigation: It looks like this may be cause by the pgrouting extension crashing the database because it is a bad query and their are not links which have this pattern.

Will post an answer soon ...




Postgres SSL SYSCALL error: EOF detected with python and psycopg

    SELECT C.id, C.name, json_agg(E) AS emails FROM contacts C
    LEFT JOIN emails E ON C.id = E.user_id
    GROUP BY C.id;

Postgres 9.3 creates output for example
       
      id  |  name  |  emails
    -----------------------------------------------------------
       1  |  Ryan  |  [{&quot;id&quot;:3,&quot;user_id&quot;:1,&quot;email&quot;:&quot;hello@world.com&quot;},{&quot;id&quot;:4,&quot;user_id&quot;:1,&quot;email&quot;:&quot;again@awesome.com&quot;}]
       2  |  Nick  |  [null]

As I am using a LEFT JOIN there will be cases where there is no right-table match therefore empty (null) values are substituted for the right-table columns. As a result I am getting `[null]` as one of the JSON aggregates.

How can I ignore/remove `null` so I have an empty JSON array `[]` when the right-table column is null?

Cheers!
    

    

Postgresql LEFT JOIN json_agg() ignore/remove NULL

sqlalchemy, a db connection module for Python, uses SQL Authentication (database-defined user accounts) by default. If you want to use your Windows (domain or local) credentials to authenticate to the SQL Server, the connection string must be changed.

By default, as defined by sqlalchemy, the connection string to connect to the SQL Server is as follows:

    sqlalchemy.create_engine(&#39;mssql://*username*:*password*@*server_name*/*database_name*&#39;)

This, if used using your Windows credentials, would throw an error similar to this:

    sqlalchemy.exc.DBAPIError: (Error) (&#39;28000&#39;, &quot;[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
    er &#39;***S\\username&#39;. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
    er &#39;***S\\username&#39;. (18456)&quot;) None None

In this error message, the code 18456 identifies the error message thrown by the SQL Server itself. This error signifies that the credentials are incorrect.

How do I connect to SQL Server via sqlalchemy using Windows Authentication?

Try as I might, I can&#39;t seem to catch the sqlalchemy IntegrityError correctly:
    
    from sqlalchemy import exc
    
    try:
        insert_record()
    except exc.IntegrityError, exc:
        print exc # this is never called
        handle_elegantly() # this is never called

As what one might expect:

    IntegrityError: (IntegrityError) insert or update on table &quot;my_table&quot; 
                    violates foreign key constraint &quot;my_table_some_column_fkey&quot;

I&#39;ve tried to explicitly:

    from sqlalchemy.exc import IntegrityError

**UPDATE:**

I found something that seems to fit what&#39;s happening here, where Integrity Error isn&#39;t thrown until the session is flushed to the db, and after the `try`/`except`blocks have been executed: https://stackoverflow.com/questions/11313935/trying-to-catch-integrity-error-with-sqlalchemy

However, adding `session.flush()` in the `try` block yields an `InvalidRequestError`:

    ERROR:root:This Session&#39;s transaction has been rolled back due to a previous 
               exception during flush. To begin a new transaction with this Session, 
               first issue Session.rollback(). 
               Original exception was: (IntegrityError)

cannot catch SQLAlchemy IntegrityError

I need to alter data during an Alembic upgrade.

I currently have a &#39;players&#39; table in a first revision:

    def upgrade():
        op.create_table(&#39;player&#39;,
            sa.Column(&#39;id&#39;, sa.Integer(), nullable=False),
            sa.Column(&#39;name&#39;, sa.Unicode(length=200), nullable=False),
            sa.Column(&#39;position&#39;, sa.Unicode(length=200), nullable=True),
            sa.Column(&#39;team&#39;, sa.Unicode(length=100), nullable=True)
            sa.PrimaryKeyConstraint(&#39;id&#39;)
        )

I want to introduce a &#39;teams&#39; table.  I&#39;ve created a second revision:

    def upgrade():
        op.create_table(&#39;teams&#39;,
            sa.Column(&#39;id&#39;, sa.Integer(), nullable=False),
            sa.Column(&#39;name&#39;, sa.String(length=80), nullable=False)
        )
        op.add_column(&#39;players&#39;, sa.Column(&#39;team_id&#39;, sa.Integer(), nullable=False))

I would like the second migration to also add the following data:

1. Populate teams table:

        INSERT INTO teams (name) SELECT DISTINCT team FROM players;

2. Update players.team_id based on players.team name:

        UPDATE players AS p JOIN teams AS t SET p.team_id = t.id WHERE p.team = t.name;

How do I execute inserts and updates inside the upgrade script?



How do I execute inserts and updates in an Alembic upgrade script?

I am using SQLite as an application file format (see [here](http://www.sqlite.org/appfileformat.html) for why you would want to do this) for my PySide-based desktop application. That is, when a user uses my app, their data is saved in a single database file on their machine. I am using the SQLAlchemy ORM to communicate with the databases.

As I release new versions of the application, I may modify the database schema. I don&#39;t want users to have to throw away their data every time I change the schema, so I need to migrate their databases to the newest format. Also, I create temporary databases a lot to save subsets of the data for use with some external processes. I want to create these databases with alembic so they are tagged with the proper version.

I have a few questions:

* Is there a way to call alembic from inside my Python code? I think it&#39;s weird to have to use `Popen` to a pure Python module, but the docs just use alembic from the command line. Mainly, I need to change the database location to wherever the user&#39;s database is located. 

* If that&#39;s not possible, can I specify a new database location from the command line without editing the .ini file? This would make calling alembic through `Popen` not a big deal.

* I see that alembic keeps its version information under a simple table called `alembic_version`, with one column called `version_num` and a single row specifying the version. Can I add an `alembic_version` table to my schema and populate it with the latest version when I create new databases so there is no overhead? Is that even a good idea; should I just use alembic to create all databases?

I have alembic working great for the single database I use to develop with in my project&#39;s directory. I want to use alembic to conveniently migrate and create databases in arbitrary locations, preferably through some sort of Python API, and not the command line. This application is also frozen with cx_Freeze, in case that makes a difference.

Thanks!

Using Alembic API from inside application code

When I&#39;m trying to create a database schema migration, I&#39;m getting this weird error. Can you please help me to figure out what&#39;s wrong?    

    $ python app.py db upgrade
    [skipped]
    sqlalchemy.exc.ArgumentError: Mapper Mapper|EssayStateAssociations|essay_associations could not assemble any primary key columns for mapped table &#39;essay_associations&#39;

**My model:**

    class EssayStateAssociations(db.Model):
        __tablename__ = &#39;essay_associations&#39;
       
        application_essay_id = db.Column(
            db.Integer,
            db.ForeignKey(&quot;application_essay.id&quot;),
            primary_key=True),
        theme_essay_id = db.Column(
            db.Integer,
            db.ForeignKey(&quot;theme_essay.id&quot;),
            primary_key=True),
        state = db.Column(db.String, default=&quot;pending&quot;)


Content Type	Original Author	Original Content on Stackoverflow
Question	yifanwu	View Question on Stackoverflow
Solution 1 - Postgresql	Danny W. Adair	View Answer on Stackoverflow
Solution 2 - Postgresql	omokehinde igbekoyi	View Answer on Stackoverflow
Solution 3 - Postgresql	Craig Ringer	View Answer on Stackoverflow
Solution 4 - Postgresql	Genesis	View Answer on Stackoverflow
Solution 5 - Postgresql	sarvesh vazarkar	View Answer on Stackoverflow
Solution 6 - Postgresql	HARI SANKAR NAYAK	View Answer on Stackoverflow

SQLAlchemy no password supplied error

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

Solution 3 - Postgresql

Solution 4 - Postgresql

Solution 5 - Postgresql

Solution 6 - Postgresql

How to enable request scope in async task executor

OSError - Errno 13 Permission denied

Attributions