OpenClinica 用户手册/离线分发
肯尼亚内罗毕的 DNDi 数据中心一直在探索使用 OpenClinica 的“离线模式”的方法，并开发了一个 python 脚本，使在离线模式下使用该软件并在稍后与中央数据库同步成为可能。该脚本旨在在一个组织结构如下所示的环境中工作；
1. 首先在主研究服务器上设置中央数据库。创建研究数据库的最终版本，并定义研究站点和站点用户。这将成为主要研究数据库或中央研究数据库，在本例中，它托管在内罗毕的 DNDi 数据中心服务器上。完成后，我们使用最终研究数据库转储来复制所有站点计算机上的数据库。
2. 在站点收集数据 在站点计算机上安装数据库后，站点用户可以正常进行数据输入。不需要互联网连接，因为 OpenClinica 在站点计算机上本地安装。然后定期从研究计算机中以 CDISC ODM 1.3 Xml 格式提取数据，并发送到内罗毕的 DNDi 数据中心，以便导入到中央数据库。最新的数据库转储也会生成并发送到数据中心。
3. 与数据中心的中央研究数据库同步。当数据中心收到提取的数据和最新的数据库转储时，最新的数据库转储将被插入与中央研究数据库位于同一服务器上的 Postgres 数据库中。这意味着在与中央数据库相同的服务器上创建了一个“使用站点名称”的数据库，并将站点数据库转储恢复到其中。一个 python（参见 https://www.pythonlang.cn）脚本，称为 OC 事件调度程序，将被执行。该脚本将中央数据库与插入的站点数据库进行比较，检查站点数据库中可用的受试者是否也在中央数据库中可用。如果受试者在中央数据库中不可用，那么它们将在中央数据库中创建。该脚本还检查站点数据库中的受试者事件是否已在中央数据库中安排，如果没有，则安排这些事件。一旦添加了新的受试者并将所有事件安排好，提取的站点数据就会使用 OpenClinica 数据导入功能导入。数据导入可以手动完成，也可以安排导入任务，以便 OpenClinica 按照计划导入放在导入文件夹中的所有 ODM 文件。
OC 事件调度程序是一个通用工具，仅提供中央数据库名称、站点数据库名称、数据库用户名、数据库主机地址和数据库密码，以便用于同步站点和中央数据库。
Python 脚本安装
要执行脚本，必须安装以下软件；
1. 除了标准的 OpenClinica 软件堆栈之外，还必须安装 Python 软件（来自 https://www.pythonlang.cn/download/releases/2.7.2/），以便执行 OC 事件调度程序脚本。
2. Psycopg2（用于 Python 编程语言的 PostgreSQL 数据库适配器）可从 http://initd.org/pub/software/psycopg2 下载，也可从 http://www.stickpeople.com/projects/python/win-psycopg/ 为 Windows 下载。
关于脚本工作原理的更多详细信息在脚本中以注释的形式提供。
import csv
import psycopg2
"""
@Author: Michael Ochieng
Data Manager/ Programmer
DNDi Africa Liason Office
Nairobi, Kenya.
Pythons Script For Bulk Scheduling of OpenClinica Events in the Central Database
- In preparation of Bulk Import from a site database.
------------------------------------------------------------------------------------------------
Assumptions
------------------------------------------------------------------------------------------------
1. The Site Database Must be a replica of the Central Database.
2. The Site database backup dump must be plugged into the same server containing the central database
"""
def __getDBConnection(central_dbname,site_dbname,username,host,passwd):
try:
"""Connection to site database i.e the database to import data from"""
conn = psycopg2.connect("dbname='"+site_dbname+"' user='"+username+"' host='"+host+"' password='"+passwd+"'")
cur=conn.cursor()
cur3=conn.cursor()
"""Conncetion to the Main Study Database i.e the database to import data to"""
conn2 = psycopg2.connect("dbname='"+central_dbname+"' user='"+username+"' host='"+host+"' password='"+passwd+"'")
cur2=conn2.cursor()
"""
1. We begin by importing the subject from OpenClinica subject table.
First, select all the study_subjects from the site study database so the we can
compare with study_subjects from the Main study database whether they are already
in the system or not.
"""
print "------------Importing Subject and Study_Subject data---------------------- "
cur.execute("""select * from study_subject;""")
study_subject_rows = cur.fetchall()
the_subject_id=0
if study_subject_rows:
for row in study_subject_rows:
cur2.execute("""select * from study_subject where label='"""+row[1]+"""'""")
site_study_subject_rows=cur2.fetchall()
"""
Meaning that if the subject with the label passed in the query above
is not present, then go ahead and add the subject in the main database.
"""
if len(site_study_subject_rows)==0:
cur3.execute("""select * from subject where subject_id=(select subject_id from study_subject where label='"""+row[1]+"""');""")
rows3=cur3.fetchall()
if rows3:
for row3 in rows3:
print row3
subjectStr="INSERT INTO subject(subject_id, father_id, mother_id, status_id, date_of_birth, gender, unique_identifier, date_created,"
subjectStr+="owner_id, date_updated, update_id,dob_collected) VALUES (nextval('subject_subject_id_seq'),%s, %s, %s,"
subjectStr=subjectStr % ('null','null',row3[3])
#Check if Date of Birth is Null
if row3[4] is None:
subjectStr+="null,"
else:
subjectStr+="'%s',"
subjectStr=subjectStr % (row3[4])
#Check if Gender is Null
if row3[5] is None:
subjectStr+="null,"
else:
subjectStr+="'%s',"
subjectStr=subjectStr % (row3[5])
subjectStr+=" '%s', '%s', %s, %s,%s, '%s');"
subjectStr=subjectStr % (row3[6],row3[7],row3[8],'null','null',row3[11])
cur2.execute(subjectStr)
conn2.commit()
strSql="INSERT INTO study_subject(study_subject_id, label, secondary_label, subject_id, study_id,status_id, enrollment_date,"
strSql+="date_created, date_updated, owner_id, update_id,oc_oid) VALUES (nextval('study_subject_study_subject_id_seq'),'%s',"
strSql+="'%s', currval('subject_subject_id_seq'), %s, %s, '%s', '%s', %s, %s,%s,'%s');"
strSql = strSql % (row[1],row[2],row[4],row[5],row[6],row[7],'null',row[9],'null',row[11])
cur2.execute(strSql)
conn2.commit()
cur2.execute("""select study_subject_id,label from study_subject;""")
ss_rows=cur2.fetchall()
if ss_rows:
for ss_row in ss_rows:
print "------------Importing Study_Event data----------------------Subject ID: ", ss_row[0]
cur.execute("""select * from study_event where study_subject_id=(select study_subject_id from study_subject where label='"""+str(ss_row[1])+"""');""")
site_study_event_rows=cur.fetchall()
if site_study_event_rows:
for i in site_study_event_rows:
selEventStr="select * from study_event where study_subject_id=(select study_subject_id from study_subject where label='%s') and study_event_definition_id=%s and sample_ordinal=%s;"
selEventStr=selEventStr %(str(ss_row[1]),str(i[1]),str(i[4]))
#print selEventStr
cur2.execute(selEventStr)
maindb_study_event_rows=cur2.fetchall()
if len(maindb_study_event_rows)==0:
eventStr="INSERT INTO study_event(study_event_id, study_event_definition_id, study_subject_id,location, sample_ordinal,"
eventStr+="date_start, date_end, owner_id, status_id,date_created, date_updated, update_id, subject_event_status_id,"
eventStr+="start_time_flag, end_time_flag) VALUES (nextval('study_event_study_event_id_seq'),%s,%s,'%s',%s,%s,%s,%s,%s,%s,%s,%s,1,'%s','%s');"
eventStr=eventStr %(i[1],ss_row[0],'null' if i[3].find('None')!=-1 else i[3],i[4],'null' if str(i[5]).find('None')!=-1 else "'"+str(i[5])+"'",'null' if str(i[6]).find('None')!=-1 else "'"+str(i[6])+"'",i[7],i[8],'null' if str(i[9]).find('None')!=-1 else "'"+str(i[9])+"'",'null' if str(i[10]).find('None')!=-1 else "'"+str(i[10])+"'",'null' if str(i[11]).find('None')!=-1 else i[11],i[13],i[14])
#print eventStr
cur2.execute(eventStr)
conn2.commit()
cur.close()
conn.close()
except psycopg2.DatabaseError, e:
print 'Error %s' % e
#return null
"""
@CentralDatabaseName: refers to the Central OC Database Name
@SiteDatabaseName: Refers to the database name for the site database as restored on the main server
@Username: Database user assigned to both databases
@localhostOrServeIP: The server IP Address or localhost if the script is executed from the server
@YourPassword: Refers to the user password corresponding to the @Username
Assumption
----------
1. The Same database login role is used when creating the CentralDatabase and the Site Database.
"""
__getDBConnection('CentralDatabaseName','SiteDatabaseName','UserName','localhostOrServeIP','YourPassword')