PostgreSQL rowcount with SQLAlchemy/psycopg2 Streaming Cursor

SQLAlchemy ResultProxy.rowcount does work with SELECT statements when using psycopg2 with PostgreSQL, despite the warnings - as long as you aren’t streaming the results. This is because psycopg2 uses libpq PQexec along with PQcmdTuples to retreive the result count (PQexec always collects the command’s entire result, buffering it in a single PGresult).

Using SQLAlchemy stream_results causes psycopg2 to use a named server-side cursor via PostgreSQL DECLARE. This will stream result records on demand, but ResultProxy.rowcount will not reflect the total result count.

To workaround this you can configure the psycopg2 server-side cursor to be scrollable (this allows moving backwards in the resultset). Then after the streaming query, execute MOVE FORWARD ALL to move to the end of the results without fetching any. PQcmdTuples will then set the pscyopg2 rowcount and you can then scroll absolute back to the beginning of the results and process them streaming.

>>> import sqlalchemy as sa >>> >>> table = sa.Table("testable", sa.MetaData(),...
      
      Read More


  
        

      AWS VPN with Public Subnet

      
        This describes how to configure an ipsec VPN in an AWS VPC with a customer who does not allow RFC-1918 (private) IP addresses in the VPC subnet.
 The basic idea is to expose a single host in the VPN using a /32 subnet of the VPN public IP. We can restrict each client peer to a specific port on that host and use port forwarding to connect them to internal hosts on private subnets in the VPC. So we can support multiple clients, and each client sees only a single host (with a public IP) and can access a single client specific port on that host.
 The following applies to Ubuntu 14.04 and Strongswan 5.1.2. For purposes of discussion we have two clients. All public IPs are invalid examples. First, clientA with peer public IP 165.{A}.22.101 and an internal host with public IP 165.{A}.22.102. We will restrict clientA to port 2575. Second, clientB with peer public IP 180.{B}.89.101 and an internal host with public IP 180.{B}.89.102. We will restrict clientB to port 2576.
 We create an EC2 instance in a VPC with CIDR 10.0.0.0/16 and place it in a public subnet with CIDR 10.0.0.0/24 and...
      

      Read More
    
  
        

      IPsec Private Subnet

      
        Sometimes you want to secure local traffic in a private subnet for compliance reasons, e.g. HIPAA requires data in-transit to be encrypted. This can be done at the application level if the application supports SSL, but it can also be done independent of the application using IPsec transport layer encryption.
 In this example we have an AWS VPC with three Ubuntu 14.04 database instances in a private subnet running a mongodb replicaset with three members. We want to encrypt all mongodb traffic between replicaset members and between other client instances in the VPC and the mongodb instances - and we won’t use mongodb SSL support.
 The mad-hacking site has a good discussion of IPsec, racoon and setkey.
 First install ipsec-tools and racoon on each instance: sudo apt-get install ipsec-tools racoon.
 Create a pre-shared key file for use with ISAKMP. We are using the wildcard * to match all hosts, so all instances are using the same key. Generate this file once and install the same file on all instances:
 Generate the file with a random key:
 [email protected]$ echo "* " $(openssl rand -base64 48) >...
      


      Read More
    

  
        

      Key Value Observing (KVO) with Swift Closures

      
        This is an Swift class to allow KVO observing using Swift closures, useable from a Swift class that does not subclass NSObject.
 From Swift, create a KeyValueObserver instance with the object being observed, the key path to observe and a closure to be called. As long as this instance remains alive, observations will be reported to the closure. To remove the observer, release the KeyValueObserver instance (so assign it to an optional so you can assign that to nil to release it).
 let button = UIButton() var kvo: KeyValueObserver? = KeyValueObserver(source: button, keyPath: "selected", options: .New) { (kvo, change) in NSLog("observing %@ %@", kvo.keyPath, change) } button.selected = true button.selected = false kvo = nil button.selected = true 
 You can save the observer in an optional member and release...
      

      Read More
    
  
        

      Installing Apple's Java in Mavericks

      
        Mavericks doesn’t have Java installed by default. If you try to use the /usr/bin/java executable from Terminal, it will pop up a dialog prompting you to visit Oracle’s site and download a JDK.

$ /usr/bin/java




If you want to install Apple’s Java instead, then run java from a subshell. This will pop up a dialog prompting you to directly install Apple’s JDK.

$ `/usr/bin/java`




The Java installer stub /System/Library/Java/Support/CoreDeploy.bundle/Contents/Download\ Java\ Components.app appears to check if it was invoked from an interactive TTY or not, and changes behavior accordingly.

      

      Read More
    
  
        

      Installing M2Crypto in a Python virtualenv on Ubuntu 13.10 Saucy

      
        There are two bugs that prevent the python M2Crypto 0.21.1 package from being pip installed from pypi in a virtualenv on Ubuntu 13.10.
   First bug #696327. libssl-dev is now multiarch and M2Crypto can’t find the opensslconf.h header:
  SWIG/_evp.i:12: Error: Unable to find 'openssl/opensslconf.h' SWIG/_ec.i:7: Error: Unable to find 'openssl/opensslconf.h' error: command 'swig' failed with exit status 1 
 
 
  Second bug #637750. If you do get M2Crypto to build by adding /usr/include/x86_64-linux-gnu/ to include_dirs in ~/.pydistutils.cfg, SSLv2 has been disabled in openssl and M2Crypto fails to import:
  >>> import M2Crypto Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/home/ubuntu/ENV/local/lib/python2.7/site-packages/M2Crypto/__init__.py", line 22, in <module> import __m2crypto ImportError: /home/ubuntu/ENV/local/lib/python2.7/site-packages/M2Crypto/__m2crypto.so: undefined symbol: SSLv2_method 
 
 
 
 The Ubuntu python-m2crypto package has patches for both these, in m2crypto_0.21.1-3ubuntu3.debian.tar.gz available here.
 These have been merged into the debian git repository, so the easiest route is to use pip’s git support and install directly from there. The patches require you to specify your architecture using an environment variable so the full command for 64 bit would be:
 

      Read More