1#!/usr/local/bin/python3.8 2 3# $Id: sample5.py 488869 2016-01-06 16:27:57Z ucko $ 4# =========================================================================== 5# 6# PUBLIC DOMAIN NOTICE 7# National Center for Biotechnology Information 8# 9# This software/database is a "United States Government Work" under the 10# terms of the United States Copyright Act. It was written as part of 11# the author's official duties as a United States Government employee and 12# thus cannot be copyrighted. This software/database is freely available 13# to the public for use. The National Library of Medicine and the U.S. 14# Government have not placed any restriction on its use or reproduction. 15# 16# Although all reasonable efforts have been taken to ensure the accuracy 17# and reliability of the software and data, the NLM and the U.S. 18# Government do not and cannot warrant the performance or results that 19# may be obtained by using this software or data. The NLM and the U.S. 20# Government disclaim all warranties, express or implied, including 21# warranties of performance, merchantability or fitness for any particular 22# purpose. 23# 24# Please cite the author in any work or product based on this material. 25# 26# =========================================================================== 27# 28# File Name: sample5.py 29# 30# Author: Sergey Sikorskiy 31# 32# Description: a NCBI DBAPI Python extension module usage example. 33# (execute a SQL query with many parameters simultaneously ("executemany")) 34# 35# =========================================================================== 36 37# 1) Import NCBI DBAPI Python extension module 38import python_ncbi_dbapi 39 40# The shared connection object 41conn = None 42 43# All code gets the connection object via this function 44def getCon(): 45 global conn 46 return conn 47 48# Create the schema and make sure we're not accessing an old, incompatible schema 49def CreateSchema(): 50 # Allocate a cursor 51 cu = getCon().cursor() 52 53 # Execute a SQL statement. 54 cu.execute("SELECT name from sysobjects WHERE name = 'customers' AND type = 'U'") 55 56 if len(cu.fetchall()) > 0: 57 # Drop the table 58 cu.execute("DROP TABLE customers") 59 60 # Create new table. 61 cu.execute(""" 62 CREATE TABLE customers ( 63 cust_name VARCHAR(255) NOT NULL 64 ) 65 """) 66 getCon().commit() 67 68def GetCustomers(): 69 # Allocate a cursor 70 cu = getCon().cursor() 71 72 # Execute a SQL statement. 73 cu.execute("select * from customers") 74 75 # Fetch all records using 'fetchall()' 76 print(cu.fetchall()) 77 78def DeleteCustomers(): 79 # Allocate a cursor 80 cu = getCon().cursor() 81 82 # Execute a SQL statement. 83 cu.execute("delete from customers") 84 85 # Commit a transaction 86 getCon().commit() 87 88def CreateCustomers(): 89 # Allocate a cursor 90 cu = getCon().cursor() 91 92 sql = "insert into customers(cust_name) values (@name)" 93 94 # Insert customers with invalid names. 95 # Execute a SQL statement with many parameters simultaneously. 96 cu.executemany(sql, [{'@name':'1111'}, {'@name':'2222'}, {'@name':'3333'}]) 97 98 # Rollback transaction 99 getCon().rollback() 100 101 # Execute a SQL statement with many parameters simultaneously. 102 cu.executemany(sql, [{'@name':'Jane'}, {'@name':'Doe'}, {'@name':'Scott'}]) 103 104 # Commit transaction 105 getCon().commit() 106 107def main(): 108 global conn 109 110 # Connect to a database 111 # Set an optional parameter "use_std_interface" to "True" 112 conn = python_ncbi_dbapi.connect('ftds', 'MSSQL', 'MSDEV1', 'DBAPI_Sample', 'DBAPI_test', 'allowed', True) 113 114 CreateSchema() 115 116 CreateCustomers() 117 118 GetCustomers() 119 120 # Delete the customer, and all her orders. 121 DeleteCustomers() 122 123 124if __name__ == "__main__": 125 main() 126