[postgis-users] How to sql scripts in a file?

[John Zhang]

Hello Giuseppe and  Sándor Daku,

Thank you for the input. Yes, after conn.commit() is called, it works.

Cheers,
John

On Thu, Apr 23, 2020 at 9:37 AM Giuseppe Broccolo <g.broccolo.7 at gmail.com>
wrote:

> Hi John,
>
> I guess you are using Psycopg2 as driver to PostgreSQL here (please add
> further details about your setup).
>
> Assuming Psycopg2: I guess that is because actions are not committed in
> your snippet. You may be interested to
> set autocommit for the execution of the script, even better to manage
> properly transactions, see the documentation
> here: https://www.psycopg.org/docs/usage.html#transactions-control
>
> For instance, I'd use context managers for connectors and cursors
> (available from version 2.5):
>
> sqlF = r'{}\{}'.format(sFldr_sql,sSQLFile)
> with psycopg2.connect(...) as conn:
>     with conn.cursor() as cur:
>         curs.execute(open(sqlF,'r').read())
>
> So that if no exception has been raised, the transaction is committed. In case of exception the transaction
> is rolled back. Also the cursor is then properly closed, without affecting the transaction.
>
> Giuseppe.
>
>
> Il giorno gio 23 apr 2020 alle ore 13:43 John Zhang <johnzhang06 at gmail.com>
> ha scritto:
>
>> Hello all,
>>
>> I have a need to execute sql scripts in a file with comments in Python
>> 2.7.
>> here is m code snippet:
>>
>> sqlF = r'{}\{}'.format(sFldr_sql,sSQLFile)
>> sql_file_contents = open(sqlF,'r').read()
>> cur.execute(sql_file_contents)
>>
>> the file can be executed from pgAdmin successfully. However, the code
>> above runs quietly without any issues raised but NO results was worked out.
>>
>> In the sql file, there are scripts to DROP FUNCTION  IF EXISTS and CREATE
>> OR REPLACE FUNCTION.
>>
>> Your input to shed light on it would be much appreciated.
>>
>> Thank you
>>
>> --
>> Yours sincerely,
>>
>>
>> John Zhang
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>

-- 
Yours sincerely,


John Zhang
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200423/4eb82da8/attachment.html>