I asked about the same thing on the SQLAlchemy Google group, and I got a recipe that appears to work well (all my tables are emptied). See [the thread][1] for reference.

My code (excerpt) looks like this:

    import contextlib
    from sqlalchemy import MetaData

    meta = MetaData()

    with contextlib.closing(engine.connect()) as con:
        trans = con.begin()
        for table in reversed(meta.sorted_tables):
            con.execute(table.delete())
        trans.commit()

Edit: I modified the code to delete tables in reverse order; supposedly this should ensure that children are deleted before parents.

  [1]: http://groups.google.com/group/sqlalchemy/browse_thread/thread/f3f24131164eb93

For PostgreSQL using `TRUNCATE`:

    with contextlib.closing(engine.connect()) as con:
        trans = con.begin()
        con.execute(&#39;TRUNCATE {} RESTART IDENTITY;&#39;.format(
            &#39;,&#39;.join(table.name 
                     for table in reversed(Base.metadata.sorted_tables))))
        trans.commit()

Note: `RESTART IDENTITY;` ensures that all sequences are reset as well. However, this is slower than the `DELETE` recipe by @aknuds1 by 50%.

Another recipe is to drop all tables first and then recreate them. This is slower by another 50%:

    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)


How about using truncate:

TRUNCATE [ TABLE ] name [, ...]

([http://www.postgresql.org/docs/8.4/static/sql-truncate.html][1])

This will delete all the records in the table, but leave the schema in tact.


  [1]: http://www.postgresql.org/docs/8.4/static/sql-truncate.html

The [HTTP/1.1 specification (RFC 2616)][1] has the following to say on the meaning of [status code 400, Bad Request (&#167;10.4.1)][2]:

&gt; The request could not be understood by
&gt; the server due to malformed syntax.
&gt; The client SHOULD NOT repeat the
&gt; request without modifications.

There seems to be a general practice among a few HTTP-based APIs these days to use 400 to mean a *logical* rather than a *syntax* error with a request. My guess is that APIs are doing this to distinguish between [400][3] (client-induced) and [500][4] (server-induced). Is it acceptable or incorrect to use 400 to indicate non-syntactic errors? If it is acceptable, is there an annotated reference on RFC 2616 that provides more insight into the intended use of 400?

Examples:

  * [Google Data Protocol, Protocol Reference, HTTP Status Codes][5]


  [1]: http://www.w3.org/Protocols/rfc2616/rfc2616.html
  [2]: http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1
  [3]: http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1
  [4]: http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.5.1
  [5]: http://code.google.com/apis/gdata/docs/2.0/reference.html#HTTPStatusCodes

HTTP 400 (bad request) for logical error, not malformed request syntax

I&#39;m working on a typing-tutor application for Mac OS X that needs to have keystrokes forwarded to it, even when the application is not in focus.

Is there a way to have the system forward keystrokes to the app, possibly through NSDistributedNotificationCenter? I&#39;ve googled myself silly, and haven&#39;t been able to find an answer...

**EDIT:** _Sample code below_.

Thanks @NSGod for pointing me in the right direction -- I ended up adding a [global events monitor](https://developer.apple.com/documentation/appkit/nsevent/1535472-addglobalmonitorforeventsmatchin) using the method `addGlobalMonitorForEventsMatchingMask:handler:`, which works beautifully. For completeness, my implementation looks like this:


    // register for keys throughout the device...
    [NSEvent addGlobalMonitorForEventsMatchingMask:NSKeyDownMask
                                           handler:^(NSEvent *event){
        
        NSString *chars = [[event characters] lowercaseString];
        unichar character = [chars characterAtIndex:0];
        
        NSLog(@&quot;keydown globally! Which key? This key: %c&quot;, character);
        
    }];

For me, the tricky part was using blocks, so I&#39;ll give a little description in case it helps anyone:

The thing to notice about the above code is that it&#39;s all **one single method call** on NSEvent. The block is supplied as an argument, directly **to** the function. You could think of it kind of like an inline delegate method. Just because this took a while to sink in for me, I&#39;m going to work through it step by step here:

    [NSEvent addGlobalMonitorForEventsMatchingMask:NSKeyDownMask

This first bit is no problem. You&#39;re calling a class method on NSEvent, and telling it which event you&#39;re looking to monitor, in this case NSKeyDownMask. A [list of masks for supported event types](http://developer.apple.com/library/mac/#DOCUMENTATION/Cocoa/Reference/ApplicationKit/Classes/NSEvent_Class/Reference/Reference.html%23//apple_ref/occ/clm/NSEvent/addGlobalMonitorForEventsMatchingMask:handler:) can be found [here](http://developer.apple.com/library/mac/#DOCUMENTATION/Cocoa/Reference/ApplicationKit/Classes/NSEvent_Class/Reference/Reference.html%23//apple_ref/occ/clm/NSEvent/addGlobalMonitorForEventsMatchingMask:handler:).

Now, we come to the tricky part: handler, which expects a block:

    handler:^(NSEvent *event){

It took me a few compile errors to get this right, but (thank you Apple) they were very constructive error messages. The first thing to notice is the carat ^. That signals the start of the block. After that, within the parentheses, 

    NSEvent *event

Which declares the variable that you&#39;ll be using within the block to capture the event. You could call it 

    NSEvent *someCustomNameForAnEvent

doesn&#39;t matter, you&#39;ll just be using that name within the block. Then, that&#39;s just about all there is to it. Make sure to close your curly brace, and bracket to finish the method call:

    }];

And you&#39;re done! This really is kind of a &#39;one-liner&#39;. It doesn&#39;t matter where you execute this call within your app -- I do it in the AppDelegate&#39;s applicationDidFinishLaunching method. Then, within the block, you can call other methods from within your app.


OS X: Detect system-wide keyDown events?

I&#39;m developing a Pylons app which is based on exisitng database, so I&#39;m using reflection. I have an SQL file with the schema that I used to create my test database. That&#39;s why I can&#39;t simply use `drop_all` and `create_all`.

I would like to write some unit tests and I faced the problem of clearing the database content after each test. I just want to erase all the data but leave the tables intact. Is this possible?

The application uses Postgres and this is what has to be used also for the tests.

SQLAlchemy, clear database content but don&#39;t drop the schema

I'm developing a Pylons app which is based on exisitng database, so I'm using reflection. I have an SQL file with the schema that I used to create my test database. That's why I can't simply use <code>drop_all</code> and <code>create_all</code>.
I would like to write some unit tests and I faced the problem of clearing the database content after each test. I just want to erase all the data but leave the tables intact. Is this possible?
The application uses Postgres and this is what has to be used also for the tests.

    import collections

    data = [
      {&#39;firstname&#39;: &#39;John&#39;, &#39;lastname&#39;: &#39;Smith&#39;}, 
      {&#39;firstname&#39;: &#39;Samantha&#39;, &#39;lastname&#39;: &#39;Smith&#39;}, 
      {&#39;firstname&#39;: &#39;shawn&#39;, &#39;lastname&#39;: &#39;Spencer&#39;}, 
    ]

    new_data = collections.defaultdict(list)

    for d in data:
        new_data[d[&#39;lastname&#39;]].append(d[&#39;firstname&#39;])

    print new_data

Here&#39;s the output:
    
    defaultdict(&lt;type &#39;list&#39;&gt;, {&#39;Smith&#39;: [&#39;John&#39;, &#39;Samantha&#39;], &#39;Spencer&#39;: [&#39;shawn&#39;]})


and here&#39;s the template: 

    {% for lastname, firstname in data.items %}
      &lt;h1&gt; {{ lastname }} &lt;/h1&gt;
      &lt;p&gt; {{ firstname|join:&quot;, &quot; }} &lt;/p&gt;
    {% endfor %}

But the loop in my template doesn&#39;t work. Nothing shows up. It doesn&#39;t even give me an error. How can i fix this? It&#39;s supposed to show the lastname along with the firstname, something like this: 

    &lt;h1&gt; Smith &lt;/h1&gt;
    &lt;p&gt; John, Samantha &lt;/p&gt;

    &lt;h1&gt; Spencer &lt;/h1&gt;
    &lt;p&gt; shawn &lt;/p&gt;


Django template can&#39;t loop defaultdict

I&#39;m using PIL. How do I turn the EXIF data of a picture into a dictionary?

In Python, how do I read the exif data for an image?

I want to have a for loop like so:

    for counter in range(10,0):
           print counter,

and the output should be 10 9 8 7 6 5 4 3 2 1


Decrementing for loops

When I run the following command:

    sudo pip install python-ldap

I get this error:

&gt;In file included from Modules/LDAPObject.c:9:
&gt;
&gt;Modules/errors.h:8: fatal error: lber.h: No such file or directory

Any ideas how to fix this?


I can&#39;t install python-ldap

I have a line of the following code (don&#39;t blame for naming conventions, they are not mine):

    subkeyword = Session.query(
        Subkeyword.subkeyword_id, Subkeyword.subkeyword_word
    ).filter_by(
        subkeyword_company_id=self.e_company_id
    ).filter_by(
        subkeyword_word=subkeyword_word
    ).filter_by(
        subkeyword_active=True
    ).one()

I don&#39;t like how it looks like (not too readable) but I don&#39;t have any better idea to limit lines to 79 characters in this situation. Is there a better way of breaking it (preferably without backslashes)?

How to break a line of chained methods in Python?

I&#39;m constructing a query using SQLAlchemy and SQLite3 in which I&#39;d like to select rows in which a String column contains a particular substring. What is the best way to accomplish this?  

SQLAlchemy query where a column contains a substring

I&#39;ve got a query:  

    MyModel.query.filter(Mymodel.name.contains(&#39;a_string&#39;))

I need to do the same query but with the negation (a **not like** operator) but didn&#39;t find any operator matching my need in the &lt;a href=&quot;http://www.sqlalchemy.org/docs/orm/tutorial.html#common-filter-operators&quot;&gt;SQLAlchemy documentation&lt;/a&gt;.

Is there any way to do it without using the sql part of SQLAlchemy???


how to pass a not like operator in a sqlalchemy ORM query

Django has some good automatic serialization of ORM models returned from DB to JSON format.

How to serialize SQLAlchemy query result to JSON format? 

I tried `jsonpickle.encode` but it encodes query object itself.
I tried `json.dumps(items)` but it returns

    TypeError: &lt;Product(&#39;3&#39;, &#39;some name&#39;, &#39;some desc&#39;)&gt; is not JSON serializable

Is it really so hard to serialize SQLAlchemy ORM objects to JSON /XML? Isn&#39;t there any default serializer for it? It&#39;s very common task to serialize ORM query results nowadays.

What I need is just to return JSON or XML data representation of SQLAlchemy query result.

SQLAlchemy objects query result in JSON/XML format is needed to be used in javascript datagird (JQGrid http://www.trirand.com/blog/)

How to serialize SqlAlchemy result to JSON?

I must be missing something trivial with SQLAlchemy&#39;s cascade options because I cannot get a simple cascade delete to operate correctly -- if a parent element is a deleted, the children persist, with `null` foreign keys. 

I&#39;ve put a concise test case here:

    from sqlalchemy import Column, Integer, ForeignKey
    from sqlalchemy.orm import relationship
    
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()
    
    class Parent(Base):
        __tablename__ = &quot;parent&quot;
        id = Column(Integer, primary_key = True)
    
    class Child(Base):
        __tablename__ = &quot;child&quot;
        id = Column(Integer, primary_key = True)
        parentid = Column(Integer, ForeignKey(Parent.id))
        parent = relationship(Parent, cascade = &quot;all,delete&quot;, backref = &quot;children&quot;)
    
    engine = create_engine(&quot;sqlite:///:memory:&quot;)
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    
    session = Session()
    
    parent = Parent()
    parent.children.append(Child())
    parent.children.append(Child())
    parent.children.append(Child())
    
    session.add(parent)
    session.commit()
    
    print &quot;Before delete, children = {0}&quot;.format(session.query(Child).count())
    print &quot;Before delete, parent = {0}&quot;.format(session.query(Parent).count())
    
    session.delete(parent)
    session.commit()
    
    print &quot;After delete, children = {0}&quot;.format(session.query(Child).count())
    print &quot;After delete parent = {0}&quot;.format(session.query(Parent).count())
    
    session.close()

Output:

    Before delete, children = 3
    Before delete, parent = 1
    After delete, children = 3
    After delete parent = 0

There is a simple, one-to-many relationship between Parent and Child. The script creates a parent, adds 3 children, then commits. Next, it deletes the parent, but the children persist. Why? How do I make the children cascade delete?


SQLAlchemy: cascade delete

Here&#39;s my (PostgreSQL) table --

    test=&gt; create table people (name varchar primary key,
                                marriage_status varchar) ; 

    test=&gt; insert into people values (&#39;Ken&#39;, &#39;married&#39;);
    test=&gt; insert into people values (&#39;May&#39;, &#39;single&#39;);
    test=&gt; insert into people values (&#39;Joe&#39;, NULL);

I want to select all people that are **not** known to be married, i.e., including those with NULL marriage_status.

This does **not** work --

    test=&gt; select * from people where marriage_status != &#39;married&#39; ; 
     name | marriage_status 
    ------+-----------------
     May  | single
    (1 row)

Of course this does --

    test=&gt; select * from people where marriage_status != &#39;married&#39;
           or marriage_status is NULL ; 
     name | marriage_status 
    ------+-----------------
     May  | single
     Joe  | 

The problem is that I&#39;m accessing it from SQLAlchemy with --

    ...filter(or_(people.marriage_status!=&#39;married&#39;,
                  people.marriage_status is None))

which gets translated to --

    SELECT people.name as name,
           people.marriage_status as marriage_status
    FROM people 
    WHERE people.marriage_status != %(status_1)s OR False
    sqlalchemy.engine.base.Engine.... {&#39;status_1&#39;: &#39;married&#39;}

And does **not** work --

    test=&gt; select * from people where marriage_status != &#39;married&#39;
           or False; 
     name | marriage_status 
    ------+-----------------
     May  | single
    (1 row)

neither does --

    test=&gt; select * from people where marriage_status != &#39;married&#39;
           or NULL; 
     name | marriage_status 
    ------+-----------------
     May  | single
    (1 row)

How should I select NULL values through SQLAlchemy?

Select NULL Values in SQLAlchemy

I am working in a pyramid project and I&#39;ve the table in SQLAlchemy in declarative syntax

    &quot;&quot;&quot;models.py&quot;&quot;&quot;
    class Projects(Base):
        __tablename__ = &#39;projects&#39;
        __table_args__ = {&#39;autoload&#39;: True}

I get the results by using

    &quot;&quot;&quot;&quot;views.py&quot;&quot;&quot;
    session = DBSession()
    row_data = session.query(Projects).filter_by(id=1).one()

How can I get the column names from this result.

PS: I am unable to use [this][1] method since I am using the declarative syntax.


  [1]: http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg02188.html

Content Type	Original Author	Original Content on Stackoverflow
Question	Juliusz Gonera	View Question on Stackoverflow
Solution 1 - Python	aknuds1	View Answer on Stackoverflow
Solution 2 - Python	kolypto	View Answer on Stackoverflow
Solution 3 - Python	Joe L.	View Answer on Stackoverflow

SQLAlchemy, clear database content but don't drop the schema

Python Problem Overview

Python Solutions

Solution 1 - Python

Solution 2 - Python

Solution 3 - Python

OS X: Detect system-wide keyDown events?

HTTP 400 (bad request) for logical error, not malformed request syntax

Attributions