Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

TypeError: 'NoneType' object is not iterable (Python3 with Oracle 19c)

epipkoAug 12 2020 — edited Aug 12 2020

The following script runs fine till it hits upc_id, = cur.fetchone().

Could someone explain, please what may cause it? If I run the query in database, I get the result back (see below).

Is there a way to see exactly what Oracle runs, after variable substitution?

I suspect single quotes are not in place for bind variables, but how can I confirm?

import datetime

import cx_Oracle

import db

line_item_sku = 'Y35FLPQ_034Y_M'

x = line_item_sku.split("_")

print (x)

print ("Split-list len: "+ str(len(x)))

if len(x) == 3:

    sku_with_dimension = False

elif len(x) == 4:

    sku_with_dimension = True

print ("SKU with dimension: " + str(sku_with_dimension))

style_id = x[0]

color_id = x[1]

size_id = x[2]

if sku_with_dimension:

    dimension_id = x[3]

print ("Style: "+style_id)

print ("Color: "+color_id)

print ("Size: "+size_id)

conn = db.connect('Galo')

print ("Connected to: " + conn.version)

cur = conn.cursor()

upc_id = cur.var(str)

print ("Assigned return var")

if sku_with_dimension:

    sql = ("""     

            select upc_id                                                                      

            from sku

            where business_unit_id = '81'

            and style_id = :1

            and color_id = :2

            and identifier_id = 'EA'

            and size_id = :3

            and dimension_id = :4

        """)

    cur.execute(sql,(style_id, color_id, size_id, dimension_id))

else:

    sql = ("""     

            select upc_id                                                                      

            from sku

            where business_unit_id = '81'

            and style_id = :1

            and color_id = :2

            and identifier_id = 'EA'

            and size_id = :3                       

        """)

    cur.execute(sql,(style_id, color_id, size_id))

print ("Determined which query to run")

upc_id, = cur.fetchone()

print (upc_id)

db.disconnect(conn, cur)

Here is the output

'Y35FLPQ', '034Y', 'M']

Split-list len: 3

SKU with dimension: False

Style: Y35FLPQ

Color: 034Y

Size: M

Connected to: 19.0.0.0.0

Assigned return value

Determined which query to run

Traceback (most recent call last):  

File "c:/Python/code/test.py", line 66, in <module>

    upc_id, = cur.fetchone()

TypeError: 'NoneType' object is not iterable

If I run the query in database, I receive a result back:

enter image description here

Comments

Start by checking that SQL*Plus (not SQL Developer) can connect.
Then try a Python program that calls cx_Oracle directly (not using Django).
This post has some useful tips that apply to all apps connecting to a remote DB: https://www.thatjeffsmith.com/archive/2014/09/ora-12545-12541-12514-01017/

Roland Mueller

Before running Django app you should of course check
whether the listener is up & running in DB machine
whether you can connect from host where django app is running to DB server+DB port. DB port is 1521 by default, but in the text I saw 1539 mentioned
connection check can be done using nmap or simply the telnet client app: 'telnet DB_HOST PORT'

Shouldn't connecting to Oracle DB using the default port 1521 in this way in settings.py?
variables DB_HOSTNAME and SID_OR_SERVICE are set in same settings.py
'default': {
'ENGINE': 'django.db.backends.oracle',
'NAME': DB_HOSTNAME + ':1521/' + SID_OR_SERVICE,
'USER': 'user',
'PASSWORD': 'pass',
}
With other port 1521 has to be replaced.

One note: 'SID_OR_SERVICE' should be 'SERVICE' since the 'easy connect' syntax doesn't support SIDs.
A couple of recent blog posts might be of interest:
https://blogs.oracle.com/opal/connecting-to-oracle-cloud-autonomous-database-with-django
https://blogs.oracle.com/opal/running-oracle-database-backed-django-web-applications-on-apache

Tony007

this my setting
"""
Django settings for mysite project.

Generated by 'django-admin startproject' using Django 2.0.2.

For more information on this file, see
https://docs.djangoproject.com/en/2.0/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/2.0/ref/settings/
"""

import os

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/2.0/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = '39tl#ywdu(^i#u2f-rosga7vn)&uchp^h96myvl4wo80a=+4g*'

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = []

# Application definition

INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'accounts',
'oauth2_provider',
'rest_framework',

]

MIDDLEWARE = [
'corsheaders.middleware.CorsMiddleware',
'corsheaders.middleware.CorsPostCsrfMiddleware',
'django.middleware.security.SecurityMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
'django.contrib.auth.middleware.AuthenticationMiddleware',
'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware',
]

ROOT_URLCONF = 'mysite.urls'

TEMPLATES = [
{
'BACKEND': 'django.template.backends.django.DjangoTemplates',
'DIRS': [],
'APP_DIRS': True,
'OPTIONS': {
'context_processors': [
'django.template.context_processors.debug',
'django.template.context_processors.request',
'django.contrib.auth.context_processors.auth',
'django.contrib.messages.context_processors.messages',
],
},
},
]

WSGI_APPLICATION = 'mysite.wsgi.application'

# Database
# https://docs.djangoproject.com/en/2.0/ref/settings/#databases

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.oracle',
'NAME': 'dbname',
'USER': 'username',
'PASSWORD': 'password',
'PORT': '1539',
'HOST': '199.154.7.188'
}
}

# Password validation
# https://docs.djangoproject.com/en/2.0/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
{
'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
},
{
'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
},
{
'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
},
{
'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
},
]

# Internationalization
# https://docs.djangoproject.com/en/2.0/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True

# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/2.0/howto/static-files/

STATIC_URL = '/static/'

AUTH_USER_MODEL = 'accounts.User'

CORS_ORIGIN_ALLOW_ALL = True

OAUTH2_PROVIDER = {

this is the list of available scopes

'SCOPES': {'read': 'Read scope', 'write': 'Write scope', 'groups': 'Access to your groups'}
}

REST_FRAMEWORK = {

Use Django's standard `django.contrib.auth` permissions,

or allow read-only access for unauthenticated users.

'DEFAULT_AUTHENTICATION_CLASSES': (
'rest_framework.authentication.BasicAuthentication',
'rest_framework.authentication.SessionAuthentication',
'oauth2_provider.contrib.rest_framework.OAuth2Authentication',
)
}

telnet 199.128.2.111:1539
telnet: could not resolve 192.168.8.123:1539/telnet: Name or service not known

If you are having network issues, install SQL*Plus (not SQL Developer) on the machine where you run Python and make sure you can connect to the database using that. Then the same connection string will be usable in Django (assuming it runs in the same environment)

1 - 5

Post Details

Added on Aug 12 2020
1 comment
2,364 views