Category Archives: web development

oracle – apply query to multiple tables

Let’s say you want to grant rights on a set of tables to a given user, here is how to do it: begin for c in ( select ‘grant select on ‘||owner||’.’||table_name||’ to newuser’ cmd from all_tables where owner = ‘myowner’ and table_name like ‘TABLE_NAME_%’ ) loop execute immediate c.cmd; end loop; end; / Note: same logic can be extended to apply any type of query to multiple tables

oracle – how to dump query output into csv file via cli [solved]

A way to do it is to use sqlplus script. create file to execute with below content (vi my_query.sql): SET ECHO OFF; SET LINESIZE 32767; SET TAB OFF; SET NEWPAGE NONE; SET PAGESIZE 32767; SET LONG 100000000; SET HEADING ON; SET WRAP OFF; SET SCAN OFF; SET FEEDBACK OFF; SET SERVEROUTPUT ON; SET HEADSEP OFF; SET PAGESIZE 0; SET TRIMSPOOL ON; SET COLSEP ‘,’; ALTER SESSION SET NLS_DATE_FORMAT=’YYYYMMDD’; SPOOL /path/to/my_query.out <paste_sql_query_here> COMMIT; EXIT; call sqlplus as per below: $ORACLE_HOME/bin/sqlplus -S -l <user/password@server> @<path/to/my_query.sql> That’s it! Ouptut of run should be available under /path/to/my_query.out as specified in SPOOL variable. Tip: run SPOOL OFF; to turn […]

python 3.5 – install oracle mysql driver on ubuntu [solved]

Installing this mysql driver is surprisingly quite a painful experience. Steps Download package relevant for your distribution and python version from oracle website: https://dev.mysql.com/downloads/connector/python/ Install downloaded package: sudo dpkg -i mysql-connector-python-py3 _2.1.3-1ubuntu15.04_all.deb Go into python and run “import mysql” For all virtualenvs, simply symlink: ln -s /usr/lib/python3/dist-packages/mysql <path_to_virtual_env>/lib/pythonX.X/site-packages It should be good by now.

oracle – truncate tables based on select query

PL/SQL allows to execute sql statements using a FOR loop, looping through results of another query. In below example we truncate all tables returned by another query: — Q1: empty all audit tables –/ BEGIN — Disable constraints — Truncate tables returned by SELECT query FOR entry IN ( SELECT table_name FROM user_tables WHERE table_name like ‘%AUDIT%’ — and rownum < 3 order by table_name ) LOOP –EXECUTE IMMEDIATE ‘select * from ‘ || entry.table_name || ‘ where rownum < 10’; EXECUTE IMMEDIATE ‘truncate table ‘ || entry.table_name; END LOOP; — Enable constraints END; / Note: make sure to enclose […]

django – how to empty database [SOLVED]

To empty the database, simply run: python manage.py flush To force command line w/o having to confirm: python manage.py flush —noinput sources http://stackoverflow.com/questions/10605940/what-is-the-django-command-to-delete-all-tables https://github.com/django/django/blob/master/django/core/management/commands/flush.py

django rest framework – ensure foreignKey field is optional [SOLVED]

I am using ModelSerializers and faced hardtime ensuring foreign key could be optional: models.py: class Tag(models.Model): label = models.CharField(max_length=100) group = models.ForeignKey(TagGroup, null=True, blank=True, related_name=’tags’ class TagGroup(models.Model): label = models.CharField(max_length=100) serializers.py: class TagSerializer(serializers.ModelSerializer): class Meta: model = Tag fields = (‘id’, ‘label’, ‘group’) group = TagGroupSerializer() class TagGroupSerializer(serializers.ModelSerializer): class Meta: model = TagGroup fields = (‘id’, ‘label’,) With above setup, though foreignKey field is clearly defined as nullable and not-mandatory in models.py, i kept getting following error: ‘{“group”:[“This field may not be null.”]}’ Reason for this is that you also need to specify field as nullable and non-mandatory in the […]

Configure apache to run a django framework based project [solved]

To serve django via apache, using mod_wsgi (recommended option by django documentation), proceed as follow: install apache mod_wsgi: sudo apt-get install libapache2-mod-wsgi-py3 ensure mod_wsgi is indeed into /etc/apache2/mods-enabled: ls -l  /etc/apache2/mods-enabled/ | grep “wsgi” create a python virtualenv for your project (see Run python virtualenv with Ubuntu) go to /etc/apache2/sites-available and create file myproject.conf: sudo vi /etc/apache2/sites-available/myproject.conf Paste following content: <VirtualHost *:80> ServerName mydomain.com ServerAdmin webmaster@localhost DocumentRoot /path/to/myproject ErrorLog ${APACHE_LOG_DIR}/myproject.error.log CustomLog ${APACHE_LOG_DIR}/myproject.access.log combined Alias /static/ /path/to/myproject/static/ <Directory /path/to/myproject/static> Require all granted </Directory> <Directory /path/to/myproject/myproject> <Files wsgi.py> Require all granted </Files> </Directory> WSGIScriptAlias / /path/to/myproject/myproject/wsgi.py WSGIDaemonProcess myproject python-path=/path/to/myproject:/path/to/myproject/virtualenv/lib/python3.4/site-packages WSGIProcessGroup myproject </VirtualHost> […]

[django] add new field on serializer which is not defined on underlying model instance [SOLVED]

To add a field which will be computed on Serializer itself without flowing through underlying model instance, declare it as follows: class DbconfigExtendedSerializer(serializers.HyperlinkedModelSerializer): password_length = serializers.SerializerMethodField(‘getPasswordLength’) def getPasswordLength(self, instance): return len(instance.password) Simply declare your field of type serializers.SerializerMethodField(‘<method_name>’) and corresponding method will be called. sources http://stackoverflow.com/questions/18396547/django-rest-framework-adding-additional-field-to-modelserializer

[django] Could not resolve URL for hyperlinked relationship using view name “-detail” [SOLVED]

When trying to build my first rest-django app, i faced below error message, which was not easy to solve: Could not resolve URL for hyperlinked relationship using view name: “<model>-detail”. You may have failed to include the related model in your API, or incorrectly configured the `lookup_field` attribute on this field. For simplicity reason, below example is based on django-rest-framework tutorial, where project is named “tutorial” and one app called “snippets” is created inside this project After investigation, the following happens: in snippets/urls.py, ensure to use “name=” attribute as per below, and use the convention of ‘<model>-detail’ for detail view […]

how to setup cx_Oracle on python – windows [solved]

When trying to use cx_Oracle in python on windows, I faced following error: >>> import cx_Oracle Traceback (most recent call last): File “<stdin>”, line 1, in <module> ImportError: DLL load failed: The specified module could not be found. In my case, oracle instant client was not installed. Here is how to solve it. Steps Download and unzip oracle instant client appropriate for your configuration: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html note: on my side i downloaded basic client + odbc + jdbc + sqlplus Ensure environment variables ORACLE_HOME and PATH points to instant client folder note: in windows, go to control panel > System > […]