Python command: SQL

Use the SQL command in Python™ to access a variety of SQL functions and GainSeeker tables.

The SQL Query action can help you generate Python script for the SQL query you want to execute.

For situations where a script is constantly running - such as polling a data source for new information every few minutes - you can use the GainSeeker Console Manager module to launch and/or monitor the progress of such scripts.

See Python commands for other commands you can use with GainSeeker.

Contents [Show][Hide]

Database connections

Commands to set up temporary database connections

Commands to execute queries

When using a temporary database connection

Commands to return table names for the current configuration

Database connections

You can execute SQL queries on the current GainSeeker database connection, or you can create a new temporary connection to another database and use it to execute SQL queries.

For a temporary connection, all commands must use an index [x] to reference the temporary connection.

Commands to set up temporary database connections

Syntax	Example	Description/Remarks	New or changed in GainSeeker version
sql[x].connect()	sql[0].connect()	Attempts to connect to the database using the supplied information. Returns True or False.
sql[x].connstr	sql[0].connstr = "Server=MyServer;Database=SPC;User id=SPC;Password=SPC"	Get/Set the connection string. It is strongly recommended that you encrypt your connection string using Pwd_enc.exe, although it may be useful to leave the connection as plain text while testing the connection.
sql[x].encryptconnstr	sql[0].encryptconnstr= True	Get/Set if the connection string is to be encrypted. Defaults to True. It is strongly recommended that you encrypt your connection string using Pwd_enc.exe, although it may be useful to leave the connection as plain text while testing the connection.
sql[x].loadsavedconn(name)	sql[0].loadsavedconn("SQLServerB")	Loads a saved database connection (from the System Administration module) by specifying the connection name.
sql[x].providerstr	sql[0].providerstr = "SqlClient Data Provider"	Get/Set the database provider string.

Example using a saved database connection (created in the System Administration module):

sql[0].loadsavedconn("SQLServerB")

sql[0].connect()

Example with unencrypted connection string (for initial testing):

sql[0].providerstr = "SqlClient Data Provider"

sql[0].encryptconnstr= False

sql[0].connstr = "Server=MyServer;Database=SPC;User id=SPC;Password=SPC"

sql[0].connect()

Example with encrypted connection string:

sql[0].providerstr = "SqlClient Data Provider"

sql[0].encryptconnstr= True

sql[0].connstr = "7A7C6A6D3E6BD113E89F89CCCD26026D57F408FAEA53C306EF4369F5AC0CEC0AB9090361FA84091C6C406BEBDA73BF4CD6092657340FA679AC4E13BE75E682FA7F2726316B1E0D8A3E5E"

sql[0].connect()

Commands to execute queries

Syntax	Example	Description/Remarks	New or changed in GainSeeker version
sql.execute(sql)	sql.execute("insert into MYTABLE values (1, 2, 3)") Executes the specified SQL query on the current GainSeeker database connection. sql[0].execute("insert into MYTABLE values (1, 2, 3)") Executes the specified SQL query on temporary database connection [0] .	Executes an insert, update, or delete SQL query and returns the number of rows affected. The command returns a -1 if there was an error.
sql.getcolumn(sql, columnname)	sql.getcolumn("select HSITYPE from HSICFG", "HSITYPE") Returns a list of values from the current GainSeeker database connection. sql[0].getcolumn("select HSITYPE from HSICFG", "HSITYPE") Returns a list of values from temporary database connection [0] .	Returns a list of values, one field in a row for the specified column. The column must be part of the SELECT portion of the SQL query or an error will occur.
sql.gettable(sql)	sql.gettable("select HSITYPE from HSICFG") Returns a query result from the current GainSeeker database connection. sql[0].gettable("select HSITYPE from HSICFG") Returnsa query result from temporary database connection [0] .	Returns a query result formatted into a list where each element is a row from the query result set. Each row is a list of elements that represent each field that was queried in the row.
sql.rowcnt(sql)	sql.RowCount("select HSITYPE from HSICFG") Returns the number of rows returned by the specified query on the current GainSeeker database connection. sql[0].RowCount("select HSITYPE from HSICFG") Returns the number of rows returned by the specified query on temporary database connection [0] .	Returns the number of rows in the query result set.

When using a temporary database connection

After using the sql[x].connect() command to set up a temporary connection, you can execute queries on that connection by referencing it with an index [x] (where x is a number or string).

For example, this command for the current GainSeeker database connection

sql.gettable(sql)

becomes

sql[x].gettable(sql)

for the temporary database connection.

Commands to return table names for the current configuration

The tables used to store different types of GainSeeker information can vary based on the configuration currently in use (typically determined by which user is logged in).

These commands return the table names used by the current configuration on the current GainSeeker database connection.

Example:

In the following example, the command sql.tables.ddata returns the name of the current DMS data table. It is used in a SQL query on the default GainSeeker database connection to return all rows from this table:

mydmsdata = sql.gettable("select * from " + sql.tables.ddata)

Syntax	Description/Remarks	New or changed in GainSeeker version
sql.tables.actionspc	Returns the name of the current SPC Action table.	8.7
sql.tables.audittrail	Returns the name of the Audit Trail table.	8.7
sql.tables.causespc	Returns the name of the current SPC Cause table.	8.7
sql.tables.ddata	Returns the name of the current DMS Data table.
sql.tables.ddataaux	Returns the name of the current DMS Auxiliary table.
sql.tables.defectlist	Returns the name of the current DMS defect table.	8.9
sql.tables.dnote	Returns the name of the current DMS Note table.
sql.tables.dproc	Returns the name of the current DMS Process table.
sql.tables.dstds	Returns the name of the current DMS Standards table.
sql.tables.eventdms	Returns the name of the current DMS Event table.	8.7
sql.tables.eventspc	Returns the name of the current SPC Event table.	8.7
sql.tables.filterdms	Returns the name of the current DMS Filter table.	8.7
sql.tables.filterspc	Returns the name of the current SPC Filter table.	8.7
sql.tables.filterstatsdms	Returns the name of the current DMS Statistical Filter table. Note: This table contains DMS statistical filters and other items. To retrieve a list of the DMS statistical filters from this table, use WHERE TYPE = 99 .	8.7
sql.tables.filterstatsspc	Returns the name of the current SPC Statistical Filter table.	8.7
sql.tables.hsict	Returns the name of the current generic Control Table.
sql.tables.hsirpt	Returns the name of the current HSIRPT table (contains Dashboards, Desktops, Dynamic Reports, Inspections, Priority Lists, Column and Detail Reports, and .Launch settings).	8.7
sql.tables.hsiscripts	Returns the name of the current Python Scripts table.	8.9
sql.tables.inspectionuniqueid	Returns the name of the current Unique ID table for inspections. See also: How to delete the current Unique ID	8.9
sql.tables.tracepriority	Returns the name of the current traceability priority list table.	8.9
sql.tables.vdata	Returns the name of the current SPC Data table.
sql.tables.vdataaux	Returns the name of the current SPC Auxiliary table.
sql.tables.vnote	Returns the name of the current SPC Note table.
sql.tables.vstds	Returns the name of the current SPC Standards table.