Command lines

Not related to Pandas but very useful to run shell command, bash, console scripts... We will use subprocess.

PSQL

With the trust method (see here):

import subprocess
 
subprocess.run('psql -h localhost -p 5432 -d work -U postgres -c "SELECT NOW() ;"', shell=True)

Without the trust method, sending the Postgres password:

import subprocess
import os
 
env = os.environ.copy()
env['PGPASSWORD'] = 'MyPassword!98464'
 
subprocess.run('
    psql -h localhost -p 5432 -d work -U postgres -c "SELECT NOW() ;"', 
    shell=True, 
    env=env)

For more complex queries, INSERT, UPDATE, JOINS... Set your SQL in a Python variable then:

myComplexInsert = '''
   INSERT INTO myTable SELECT * FROM myOtherTable WHERE field_a IN (
   SELECT field_b FROM myOtherOtherTable
   blablabla...
   ) ;
'''
 
subprocess.run(['psql', '--quiet', '-h', 'localhost', '-p', '5432', '-d', 'work', '-U', 'postgres', '-c',myComplexInsert])

PgSql2Shp

subprocess.run( \
    '''pgsql2shp -f E:\\Path\\to\\MyShape -u postgres -h localhost work "SELECT * FROM MyTable ;"''', \
    shell=False, \
    executable='E:\\Path\\to\\pgsql2shp.exe' \
)

Powershell

subprocess.run( \
    '''OGR2OGR...''', \
    shell=False, \
    executable='E:\\Path\\to\\powershell.exe' \
)

Liens ou pièces jointes

France-Departements-Deformation.zip	[France-Departements-Deformation]	335 Ko
simple_countries.zip	[simple_countries]	1880 Ko