Stateframe Database - Revision history

Dgary: /* Initial setup of database access software */

2022-03-07T17:37:37Z

Initial setup of database access software

← Older revision		Revision as of 17:37, 7 March 2022
Line 70:		Line 70:
	FileUsage = 1		FileUsage = 1
	</pre>		</pre>
	6. [~~This~~ are old instructions--just follow the pypi instructions at the above link.]Unzip the zip file in step 1, cd to the directory '''pyodbc-3.0.7''', and type:		6. [These are old instructions--just follow the pypi instructions at the above link.] Unzip the zip file in step 1, cd to the directory '''pyodbc-3.0.7''', and type:
	<pre>		<pre>
	python setup.py build		python setup.py build

Dgary: /* Initial setup of database access software */

2022-03-07T17:37:04Z

Initial setup of database access software

← Older revision		Revision as of 17:37, 7 March 2022
Line 24:		Line 24:
	The Python library to access Microsoft SQL Server is called pyodbc. This describes downloading the library and integrating it with an existing '''python2.76''' installation, making a first connection to the database, and executing a query. The query assumes that database “eOVSA06” has a populated table “fV26_vD15.”		The Python library to access Microsoft SQL Server is called pyodbc. This describes downloading the library and integrating it with an existing '''python2.76''' installation, making a first connection to the database, and executing a query. The query assumes that database “eOVSA06” has a populated table “fV26_vD15.”

	1. First install '''pyodbc''' ([https://~~code~~.~~google.com~~/p/pyodbc/~~downloads/list~~]). ~~For instance, '''pyodbc-3.0.7.win-amd64-py2.7.exe'''.~~		1. First install '''pyodbc''' ([https://pypi.org/project/pyodbc/]).

	2. Start '''ipython''' and type		2. Start '''ipython''' and type
	<pre>import pyodbc</pre>		<pre>import pyodbc</pre>
Line 44:		Line 43:

	2. Install unixODBC and FreeTDS packages:		2. Install unixODBC and FreeTDS packages:
	<pre>sudo apt-get install ~~unixODBC-dev~~ freetds-dev freetds-bin tdsodbc</pre>		<pre>sudo apt-get install unixodbc freetds-dev freetds-bin tdsodbc</pre>
	3. Edit '''/etc/freetds/freetds.conf''' (or copy from helios) to change the last 4 lines to		3. Edit '''/etc/freetds/freetds.conf''' (or copy from helios) to change the last 4 lines to
	<pre>		<pre>
Line 71:		Line 70:
	FileUsage = 1		FileUsage = 1
	</pre>		</pre>
	6. Unzip the zip file in step 1, cd to the directory '''pyodbc-3.0.7''', and type:		6. [This are old instructions--just follow the pypi instructions at the above link.]Unzip the zip file in step 1, cd to the directory '''pyodbc-3.0.7''', and type:
	<pre>		<pre>
	python setup.py build		python setup.py build

Dgary: /* Complete process of inserting a new binary record */

2018-02-27T15:05:21Z

Complete process of inserting a new binary record

← Older revision		Revision as of 15:05, 27 February 2018
Line 124:		Line 124:
	f.close()		f.close()
	cnxn = pyodbc.connect("DRIVER={SQL Server};SERVER=128.235.89.168,1433;		cnxn = pyodbc.connect("DRIVER={SQL Server};SERVER=128.235.89.168,1433;
	DATABASE=eOVSA06;UID=aaa;PWD=~~I@bsbn2w~~;")		DATABASE=eOVSA06;UID=aaa;PWD=<password>;")
	cursor = cnxn.cursor()		cursor = cnxn.cursor()
	with open(‘sf_20140205_v26.0.log’, ‘rb’) as f:		with open(‘sf_20140205_v26.0.log’, ‘rb’) as f:
Line 138:		Line 138:

	The execute line may return an error and fail, of course, especially if the data have previously been inserted, so it is enclosed in a try: except: clause so that everything does not immediately stop. Error checking would go into the except: clause.		The execute line may return an error and fail, of course, especially if the data have previously been inserted, so it is enclosed in a try: except: clause so that everything does not immediately stop. Error checking would go into the except: clause.


	=== Procedure to create the information for a new stateframedef table ===		=== Procedure to create the information for a new stateframedef table ===

Dgary: /* Initial setup of database access software */

2018-02-27T15:04:18Z

Initial setup of database access software

← Older revision		Revision as of 15:04, 27 February 2018
Line 29:		Line 29:
	<pre>import pyodbc</pre>		<pre>import pyodbc</pre>
	3. Connect to database with		3. Connect to database with
	<pre>cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06; UID=aaa; PWD=~~I@bsbn2w~~;")</pre>		<pre>cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06; UID=aaa; PWD=<password>;")</pre>
	4. Get a 'cursor' to the database:		4. Get a 'cursor' to the database:
	<pre>cursor = cnxn.cursor()</pre>		<pre>cursor = cnxn.cursor()</pre>

Dgary: /* Introduction */

2018-02-27T15:03:43Z

Introduction

← Older revision		Revision as of 15:03, 27 February 2018
Line 9:		Line 9:
	</pre>		</pre>

	However, some adjustments were needed to allow connection to the actual server machine (EOVSASQL), using an IP address and “SQL Authentication.” The trick was to create a user on the server in the SQL Server Management Studio (user name is <code>‘aaa’</code>, password ~~is <code>‘I@bsbn2w’</code>~~), and then use the server IP address (currently ‘128.235.89.168’) on port 1433. This port had to be opened in the server’s Windows Firewall. The new connection string is:		However, some adjustments were needed to allow connection to the actual server machine (EOVSASQL), using an IP address and “SQL Authentication.” The trick was to create a user on the server in the SQL Server Management Studio (user name is <code>‘aaa’</code>, password provided to those who need it), and then use the server IP address (currently ‘128.235.89.168’) on port 1433. This port had to be opened in the server’s Windows Firewall. The new connection string is:
	<pre>		<pre>
	cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06;UID=aaa;PWD=~~I@bsbn2w~~;")		cnxn = pyodbc.connect("DRIVER={SQL Server}; SERVER=128.235.89.168,1433; DATABASE=eOVSA06;UID=aaa;PWD=<password>;")
	</pre>		</pre>

Line 17:		Line 17:
	As of 2014-Sep-30, the SQL Server has been set up at OVRO and the connection string from Helios is:		As of 2014-Sep-30, the SQL Server has been set up at OVRO and the connection string from Helios is:
	<pre>		<pre>
	cnxn = pyodbc.connect("DRIVER={FreeTDS}; SERVER=192.168.24.106,1433; DATABASE=eOVSA06;UID=aaa;PWD=~~I@bsbn2w~~;")		cnxn = pyodbc.connect("DRIVER={FreeTDS}; SERVER=192.168.24.106,1433; DATABASE=eOVSA06;UID=aaa;PWD=<password>;")
	</pre>		</pre>


	=== Initial setup of database access software ===		=== Initial setup of database access software ===

Dgary: /* Useful Tricks */

2017-04-25T17:45:31Z

Useful Tricks

@@ Line 220: / Line 220: @@
 == Useful Tricks ==
 === Get Every Nth Entry ===
 To get every Nth entry, for example, one data point each hour, use the modulo (%) function.  For example, this gets the VPol power for Ant 14 ['''(I15 % 15) = 13'''] once an hour ['''(cast(Timestamp as bigint) % 3600) = 0''']:
-<pre>query = 'select Timestamp,Ante_Fron_FEM_VPol_Power from fV66_vD15 where (I15 % 15) = 13 and
+<pre>import dbutil as db
 Timestamp between '+t0+' and '+t1+' and (cast(Timestamp as bigint) % 3600) = 0 order by Timestamp'
-</pre>
+data, msg = db.do_query(cursor, query)</pre>
-where t0 is the start Labview timestamp as a string, and t1 is the end Labview timestamp as a string.  Note the need to convert Timestamp (a float) to a big integer first.
+where t0 is the start Labview timestamp as a string, and t1 is the end Labview timestamp as a string.  Note the need to convert Timestamp (a float) to a big integer first.  If successful, msg will be the string 'Success' and data will be a dictionary with keys Timestamp and Ante_Fron_FEM_VPol_Power.

Dgary: /* Get Every Nth Entry */

2017-04-25T17:28:01Z

Get Every Nth Entry

← Older revision		Revision as of 17:28, 25 April 2017
Line 221:		Line 221:
	== Useful Tricks ==		== Useful Tricks ==
	=== Get Every Nth Entry ===		=== Get Every Nth Entry ===
	To get every Nth entry, for example, one data point each hour, use the modulo (%) function. For example, this gets Ant 14~~'s VPol power~~ '''(I15 % 15) = 13''' once an hour '''(cast(Timestamp as bigint) % 3600) = 0''':		To get every Nth entry, for example, one data point each hour, use the modulo (%) function. For example, this gets the VPol power for Ant 14 ['''(I15 % 15) = 13'''] once an hour ['''(cast(Timestamp as bigint) % 3600) = 0''']:
	<pre>query = 'select Timestamp,Ante_Fron_FEM_VPol_Power from fV66_vD15 where (I15 % 15) = 13 and		<pre>query = 'select Timestamp,Ante_Fron_FEM_VPol_Power from fV66_vD15 where (I15 % 15) = 13 and
	Timestamp between '+t0+' and '+t1+' and (cast(Timestamp as bigint) % 3600) = 0 order by Timestamp'		Timestamp between '+t0+' and '+t1+' and (cast(Timestamp as bigint) % 3600) = 0 order by Timestamp'
	</pre>		</pre>
	where t0 is the start Labview timestamp as a string, and t1 is the end Labview timestamp as a string.		where t0 is the start Labview timestamp as a string, and t1 is the end Labview timestamp as a string. Note the need to convert Timestamp (a float) to a big integer first.

Dgary: /* Get Every Nth Entry */

2017-04-25T17:26:45Z

Get Every Nth Entry

← Older revision		Revision as of 17:26, 25 April 2017
Line 221:		Line 221:
	== Useful Tricks ==		== Useful Tricks ==
	=== Get Every Nth Entry ===		=== Get Every Nth Entry ===
	To get every Nth entry, for example, one data point each hour, use the modulo (%) function. For example, this gets Ant 14's VPol power once an hour:		To get every Nth entry, for example, one data point each hour, use the modulo (%) function. For example, this gets Ant 14's VPol power '''(I15 % 15) = 13''' once an hour '''(cast(Timestamp as bigint) % 3600) = 0''':
	<pre>query = 'select Timestamp,Ante_Fron_FEM_VPol_Power from fV66_vD15 where (I15 % 15) = 13 and		<pre>query = 'select Timestamp,Ante_Fron_FEM_VPol_Power from fV66_vD15 where (I15 % 15) = 13 and
	Timestamp between '+t0+' and '+t1+' and (cast(Timestamp as bigint) % 3600) = 0 order by Timestamp'		Timestamp between '+t0+' and '+t1+' and (cast(Timestamp as bigint) % 3600) = 0 order by Timestamp'
	</pre>		</pre>
	where t0 is the start Labview timestamp as a string, and t1 is the end Labview timestamp as a string.		where t0 is the start Labview timestamp as a string, and t1 is the end Labview timestamp as a string.

Dgary at 17:24, 25 April 2017

2017-04-25T17:24:49Z

← Older revision		Revision as of 17:24, 25 April 2017
Line 1:		Line 1:

	== Setup and Initial Test of Python Access to Existing Database ==		== Setup and Initial Test of Python Access to Existing Database ==


	=== Introduction ===		=== Introduction ===
Line 219:		Line 218:
	* <code>cursor = get_cursor()</code> Opens the database and returns a cursor for access to it.		* <code>cursor = get_cursor()</code> Opens the database and returns a cursor for access to it.
	* <code>mydict = get_dbrecs(cursor, version, dimension, timestamp, nrecs)</code> Takes as input an open cursor and version, dimension, timestamp and nrecs, and returns a dictionary from the table indicated by the version and dimension, starting at timestamp, and having nrecs entries. The data in each mydict key has dimensions of dimension x nrecs.		* <code>mydict = get_dbrecs(cursor, version, dimension, timestamp, nrecs)</code> Takes as input an open cursor and version, dimension, timestamp and nrecs, and returns a dictionary from the table indicated by the version and dimension, starting at timestamp, and having nrecs entries. The data in each mydict key has dimensions of dimension x nrecs.

			== Useful Tricks ==
			=== Get Every Nth Entry ===
			To get every Nth entry, for example, one data point each hour, use the modulo (%) function. For example, this gets Ant 14's VPol power once an hour:
			<pre>query = 'select Timestamp,Ante_Fron_FEM_VPol_Power from fV66_vD15 where (I15 % 15) = 13 and
			Timestamp between '+t0+' and '+t1+' and (cast(Timestamp as bigint) % 3600) = 0 order by Timestamp'
			</pre>
			where t0 is the start Labview timestamp as a string, and t1 is the end Labview timestamp as a string.

Mlukicheva: /* Initial setup of database access software */

2016-09-20T18:49:51Z

Initial setup of database access software

← Older revision		Revision as of 18:49, 20 September 2016
Line 26:		Line 26:
	The Python library to access Microsoft SQL Server is called pyodbc. This describes downloading the library and integrating it with an existing '''python2.76''' installation, making a first connection to the database, and executing a query. The query assumes that database “eOVSA06” has a populated table “fV26_vD15.”		The Python library to access Microsoft SQL Server is called pyodbc. This describes downloading the library and integrating it with an existing '''python2.76''' installation, making a first connection to the database, and executing a query. The query assumes that database “eOVSA06” has a populated table “fV26_vD15.”

	1. First install '''pyodbc''' ([https://code.google.com/p/pyodbc/downloads/list]). ~~I installed~~ '''pyodbc-3.0.7.win-amd64-py2.7.exe'''.		1. First install '''pyodbc''' ([https://code.google.com/p/pyodbc/downloads/list]). For instance, '''pyodbc-3.0.7.win-amd64-py2.7.exe'''.

	2. Start '''ipython''' and type		2. Start '''ipython''' and type
Line 80:		Line 80:

	From there, use the connection string for Linux shown in the introduction section, and proceed from there.		From there, use the connection string for Linux shown in the introduction section, and proceed from there.


	=== Examples of returned queries ===		=== Examples of returned queries ===