Pulling data from an existing database

Question

I am trying to create a web app that handles account data and prints said data to account statements and age analysis.

The data will need to be fetched from an existing Sage Evolution database and printed to a web page through Django.

I have had a look at the Django documentation, however, the inspectdb function it gives does not give too much information on how to fetch data through a database IP address.

Does anyone have a code example/tutorial on how to fetch this data through an IP address, almost with the same procedure as the below .BAT code

import pymssql

user = '[User ]'
password = '[Password]'
server = '[IP]'

#username: [Username]_dev_ro
#password: [Password]
#database name: DB_Name

databaseName = 'DB_Name'

conn = pymssql.connect(server, user, password, databaseName)
cursor = conn.cursor(as_dict=True)

cursor.execute('SELECT top 10 * FROM [DB_Name].[dbo].[_etblGLAccountTypes]')
for row in cursor:
    print(row)

conn.close()

score 1 · Accepted Answer · answered Aug 19 '21 at 08:14

When using Django, you don't need to write code to manually connect to the database. You just have to configure the DATABASES field in your Django project's settings.py then Django ORM would already do the work for you. Actually, you don't even have to write SQL commands anymore (unless you have very specific use cases that are not yet covered) as Django already provides tools (via model class methods) to do it for you.

.
├── manage.py
└── my_proj
   ├── asgi.py
   ├── __init__.py
   ├── settings.py  <--- This one
   ├── urls.py
   └── wsgi.py

Sample configuration:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydatabase',
        'USER': 'mydatabaseuser',
        'PASSWORD': 'mypassword',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

Once configured, running any Django management commands that targets the database such as inspectdb would already communicate to the configured database. You might need to create models for your existing database as documented e.g. python manage.py inspectdb > models.py. Then, performing a query would be as easy as:

$ python manage.py shell
>>> from my_app import models
>>> models.ModelA.objects.all()
<QuerySet [<ModelA: ModelA object (1)>, <ModelA: ModelA object (2)>]>
>>> models.ModelA.objects.values()
<QuerySet [{'id': 1, 'child_field_id': None, 'field_a': 'Value 1'}, {'id': 2, 'child_field_id': 1, 'field_a': 'Value 2'}]>

For more options with querying, see docs.

You may even connect directly to your database via python manage.py dbshell as documented.

Pulling data from an existing database

1 Answers1