Hello,
would it be possible to access network ressources (like a database server via pymssql) from inside the test (sandbox). Or are there any limitations in accessing ressources.
Thank you!
Normally the Jobe server which runs all the jobs is tightly firewalled to prevent unauthorised accesses in or out. If you wanted to access external services from jobs running within the server you'd have to configure the firewall to allow access to those services. Other than that, there's nothing preventing such external access.
I've certainly heard of users running database-accessing jobs in CodeRunner. But you probably need to set up an account on the external DB server for every student, unless you only want to run SELECT queries.
Thanks for your reply. I'm not that familiar with Jobe server, where do I find the firewall settings?
It sounds like you're not a sysadmin, so the short answer is probably that you won't be able to change the firewall settings. You'll probably need to put in a request to your institution's IT services group or whoever installed the CodeRunner plugin, requesting that the firewall for the Jobe server be set to allow outgoing access to whatever database server you want to access. But they (IT services) will quite likely be reluctant to weaken the security in that way.
If they say no, and you're really determined to access a remote DB server, you could set up your own Jobe server (e.g. using Docker + jobeinabox) and, using advanced configuration, set the question to use that custom Jobe server. But this option is not for the faint of heart and does still require sysadmin skills.
Are you sure you can't get away with using the supplied SQL question type, which uses sqlite3? It should be fine for teaching the basics of SQL. Then you don't need special firewall settings, access to remote servers, or per-student configuration of the database. Alternatively, if you wish to specifically teach python programming in conjunction with a database, you might be able to use an sqlite3 database file loaded into the question as a support file, appropriately configured for the question, and let the students access it with the sqlite3 module in Python.
Again, thank you very much!!
My plan is to set it up for MS SQL Server and Oracle. PL/SQL divers from SQLite. I've also concept in my head for allowing DML-Statements and creating DB-sandboxes on the fly.
Thanks, it helped me a lot. I will try to asking changing firewall settings...
Finally, it's working ... and I can run Oracle queries via Coderunner. Maybe it helps someone too:
Solution:
I took jobeinabox docker image from https://github.com/trampgeek/jobeinabox and installed oracle client there.
These are the necessary steps:
sudo -H python3 -m pip install cx_Oracle
apt-get update
sudo apt-get install wget
mkdir -p /opt/oracle
cd /opt/oracle
wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basic-linux.x64-21.1.0.0.0.zip
unzip instantclient-basic-linux.x64-21.1.0.0.0.zip
sudo apt-get install libaio1
sudo sh -c "echo /opt/oracle/instantclient_21_1 > /etc/ld.so.conf.d/oracle-instantclient.conf"
sudo ldconfig
Then I'm using a python script to validate the test case:
import subprocess, sys
import cx_Oracle as oracledb
db_connection_string = '... connection string ...'
con = oracledb.connect(db_connection_string)
cursor1 = con.cursor()
student_answer = """{{ STUDENT_ANSWER | e('py') }}"""
cursor1.execute(student_answer)
cursor1.fetchall()
col_names = []
for i in range(0, len(cursor1.description)):
col_names.append(cursor1.description[i][0])
print("Expected ROWS: " + str(cursor1.rowcount))
print("Expected COLUMNS: ")
print(col_names)
print("Expected DATA (first 3 rows as example):")
number = 0
cursor1.execute(student_answer)
while True:
row = cursor1.fetchone()
if row is None:
break
print(row)
number = number + 1
if number >= 3:
break