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