Create basic objects without a migration

Using Django
1

Hi,

We use SQL server, which I have finally got connected to. Our DBAs will not allow Django to control the server objects using migrations and have given me a read/write user with no DDL permissions. So I need to give them a script with the basic structures needed for the admin functionality.

The config is basic out of the box Django:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'PollsterSystem.apps.PollstersystemConfig',
]

I eventually found I could issue the showmigrations command to see which migrations needed to be applied and that gave me:

showmigrations
bash -cl "/usr/bin/python3.8 /snap/pycharm-professional/244/plugins/python/helpers/pycharm/django_manage.py showmigrations /home/pmason/PycharmProjects/DjangoTutorial"
Tracking file by folder pattern:  migrations
PollsterSystem
 (no migrations)
admin
 [ ] 0001_initial
 [ ] 0002_logentry_remove_auto_add
 [ ] 0003_logentry_add_action_flag_choices
auth
 [ ] 0001_initial
 [ ] 0002_alter_permission_name_max_length
 [ ] 0003_alter_user_email_max_length
 [ ] 0004_alter_user_username_opts
 [ ] 0005_alter_user_last_login_null
 [ ] 0006_require_contenttypes_0002
 [ ] 0007_alter_validators_add_error_messages
 [ ] 0008_alter_user_username_max_length
 [ ] 0009_alter_user_last_name_max_length
 [ ] 0010_alter_group_name_max_length
 [ ] 0011_update_proxy_permissions
 [ ] 0012_alter_user_first_name_max_length
contenttypes
 [ ] 0001_initial
 [ ] 0002_remove_content_type_name
sessions
 [ ] 0001_initial

Process finished with exit code 0

But when I issue sqlmigrate admin 0001_initial I get lots of errors:

sqlmigrate admin 0001_initial
bash -cl "/usr/bin/python3.8 /snap/pycharm-professional/244/plugins/python/helpers/pycharm/django_manage.py sqlmigrate admin 0001_initial /home/pmason/PycharmProjects/DjangoTutorial"
Tracking file by folder pattern:  migrations
Traceback (most recent call last):
  File "/snap/pycharm-professional/244/plugins/python/helpers/pycharm/django_manage.py", line 52, in <module>
    run_command()
  File "/snap/pycharm-professional/244/plugins/python/helpers/pycharm/django_manage.py", line 46, in run_command
    run_module(manage_file, None, '__main__', True)
  File "/usr/lib/python3.8/runpy.py", line 207, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/pmason/PycharmProjects/DjangoTutorial/manage.py", line 22, in <module>
    main()
  File "/home/pmason/PycharmProjects/DjangoTutorial/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/commands/sqlmigrate.py", line 29, in execute
    return super().execute(*args, **options)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/commands/sqlmigrate.py", line 65, in handle
    sql_statements = loader.collect_sql(plan)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/db/migrations/loader.py", line 351, in collect_sql
    state = migration.unapply(state, schema_editor, collect_sql=True)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 118, in __exit__
    self.execute(sql)
  File "/home/pmason/.local/lib/python3.8/site-packages/mssql/schema.py", line 856, in execute
    sql = str(sql)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/db/backends/ddl_references.py", line 201, in __str__
    return self.template % self.parts
KeyError: 'include'

I’ve tried sqlmigrate admin and sqlmigrate admin 0001 and both generate the above.

Any suggestions, or is there another way of getting this SQL?

2

Try running the manage.py command directly from the command line and not through your editor. I’ve seen messages like this before when the path hasn’t been “right” for some reason - typically for some path issue.

3

Thanks. Tried this and got the same sort of error:

python3 manage.py sqlmigrate admin 0001_initial
Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    main()
  File "manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/commands/sqlmigrate.py", line 29, in execute
    return super().execute(*args, **options)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/core/management/commands/sqlmigrate.py", line 65, in handle
    sql_statements = loader.collect_sql(plan)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/db/migrations/loader.py", line 351, in collect_sql
    state = migration.unapply(state, schema_editor, collect_sql=True)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 118, in __exit__
    self.execute(sql)
  File "/home/pmason/.local/lib/python3.8/site-packages/mssql/schema.py", line 856, in execute
    sql = str(sql)
  File "/home/pmason/.local/lib/python3.8/site-packages/django/db/backends/ddl_references.py", line 201, in __str__
    return self.template % self.parts
KeyError: 'include'

Although the lines numbers are slightly different.

The stack trace indicates that the error involves calls to schema.py in the mssql package, so I’m gonna have chat with the authors and see what happening there. What’s the betting that the lack of DDL permissions for the user is the cause of this…better than 50 / 50 I would say.

I’ll be back!

4

It’s definitely a package issue, not permissions. The DBA gave my user DDL permissions and no change…have logged an issue with the package authors and will try to reverse engineer a SQLite DB for the script.

5

Not to be funny, but you need to lift this over the heads of your DBAs. Either that ,or use something other than Django.

If the company has selected django for dev, then restricting migrations like this makes little sense, and somebody with the authority to approve it needs to be informed and have it explained to them.

6

I’m with the DBAs…I have worked as a DBA myself in the past. The idea of letting a bunch of programmers loose with a code first ORM on a bunch of corporate databases is anathema to me and them. These are NHS databases with patient data on…other than defence/security infrastructure, these babies are as sensitive as it gets.

No, we will engage in database first ORM and from what I can see Django handles that well enough. It requires manually added metadata in the model, which is not ideal, but it’ll do for now. Would prefer to have an environment setting that configures it.

This issue is that the MSSQL package is not handling the initial migration needed for the admin tools at all…have posted an issue on their GitHub site. And have now found that the package is only in Beta. So I’m working around it at the moment…it’ll be interesting to see if I can manually con Django into thinking the migrations have been done by it when they have been done using a script.

At the moment I am the R&D person for my team. I would normally be programming in .NET, but those tools are not available to me, we have scientist types who are already using Django and my colleagues are still using MS Access (and associated 90s style approaches to working!), so they have the mother of all learning curves just for this framework. Besides, I’m curious…and so far so good. This issue is a blip…I hope.

7

Well, that being the case, I wonder why Django was chosen in the first place? It doesn’t seem like the right choice if you have those kind of considerations.

Also, haha, Access … haven’t had to work with that for a while, but still see instances of it in the wild from time to time :slight_smile: It’s not actually that far removed from MVC/MVT patterns though, so give the Access guys some credit - they may find it more intuitive than you think. From my experience the hardest part of migrating from older “legacy” (don’t like that word really) systems is not so much the new dev language/framework - it’s more the modern devops processes, use of git for version control, automated testing, CI/CD that tend to be the sticking points.

Hope it all goes well for you anyway. Also hope the NHS survives long enough to make it all worthwhile! :slight_smile:

8

I do a fair amount of work where I put a Django front-end on top of a “legacy” / “pre-existing” database. It’s absolutely the right tool for the job we’re doing. In those cases, Django does not manage those databases. (Every model is flagged “managed=False”.) In those cases, we have two databases configured, a “Django” database and the “data” database. The accounts to the “data” databases have select-only access, and we add a custom router to reject any attempt to write to those models. It all works extremely well for us.

Keep in mind that allowing Django to fully manage / update your schema is a relatively recent development. Go back in time far enough, and “migrations” wasn’t even part of Django (pre-1.7) - there was an external package named “south” (available since Django 1.2? - before my time).

9

Pre 1.7 is a long time ago now :wink:

I was recently involved in trying to move stuff to a Django based architecture from a legacy monolith, so I’ve looked at some of the things you mention. The difference there is that the end goal was to do away with the legacy part, even if it was going to take 5+ years (which was the plan).

As much as I do love Django, I can’t help feeling there would be better choices around if you weren’t going to make use of the standard stuff it provides. Not saying it’s wrong or bad - I just find it an odd choice given the constraints is all. That’s all just imho though, and if it works for people, then brilliant :slight_smile:

I also understand the restrictions when it comes to stuff like NHS patient data as well, though I also think that “locking down” devs rarely leads to good things - usually it leads to a load of odd workarounds because they can’t do certain things, and the workarounds are often more dangerous than trusting them not to be awful in the first place. Also, decent CI/CD processes should help to prevent any egregious issues here :slight_smile:

10

In our case, it’s an issue of minimizing the number of different systems being used to expose data to a web-based interface. Rather than having developers familiar with all of Access, Java/Spring, Drupal, Flask, Bottle, and R/Shiny, we’re whittling that down to just Django and R/Shiny. So while Django may not be the “optimal” tool for every task, it is “suitable” for every task, minimizes the overall load on the department and generally accelerates the rate at which work gets done.

Side note, I can’t speak to NHS data, but in the US when working with financial data, many of the restrictions come from legal, regulatory, and auditability requirements. Even the best CI/CD processes aren’t going to “protect” you in an audit if you can’t prove you don’t have access you don’t need to have - and in some cases, even having access to production data is a no-no.

1 Like
11

Reducing the number of different techs needed is definitely a good goal :slight_smile:

Yes, GDPR rules in Europe are pretty strict when it comes to data access (not to mention other rules), but not sure access to prod data is needed to run migrations on production data? (I mean, obviously something needs access to run the migrations, but that something doesn’t need to be a person).

You could even produce the SQL using the django migrations and get a signoff from the DBAs first that it was OK?

All the big systems I’ve worked on since GDPR have anonymised data in test envs or built routines to populate the test/dev envs with “fake” data. This has been a bit of a pain on occasion, but usually because it was done badly, rather than because we specifically needed real customer data.

The only case I’ve seen when access to real prod data was genuinely needed is in investigating specific incidents that cannot be reproduced in dev - often these are dependant on some specific data combination in production data that nobody really foresaw … I’m sure you’ve come across these more than once :slight_smile:

12

Note for clarity: I’m not the original poster, just sharing my experiences from what I believe to be a similar situation. (I know you’re aware of that, I’m writing this for later readers.)

In our case, the DBAs were the only people with authorization to make schema changes. Audit logging required the changes be made by a DBA account. So we could pass the SQL to them, but they were responsible for scheduling and making the changes.

Ditto. And problems usually occurred because of edge cases in the data not covered by the data generation / extracts / masking.

One such case I like to share being the time that two people were each assigned to two different “departments” - not unusual for that organization at that time - but each was the other’s supervisor in the two cases. e.g. “A” reported to “B” in “X”, but “B” reported to “A” in “Y”. And in context, it was a perfectly valid situation. (A long time ago in a company far, far away…)

13

DBAs rarely catch edge cases in data from what I’ve seen - they’re concerned with consistency, optimisation and performance mainly. As they should be :slight_smile:

I do think the days of “hail the almighty DBA” have largely come to an end though, and modern DBAs need to work more closely with devs and perhaps allow a little more latitude to some trusted people (not everybody of course!)

Same goes for sysadmins to an extent. They serve a very clear purpose, but should not be allowed to wield the ultimate power that they perhaps used to back in the day :slight_smile:

This has been an interesting discussion though - even if we’ve digressed a little from the original question. Thanks! :slight_smile:

1 Like
14

I can honestly say you are the first IT person in a long while who hasn’t sneered at the choice of using MS Access. I’ve been using it for 25 years since v1.0 and have always thought it was an excellent tool if used in the right way, right place…Here it is used in the wrong way to provide systems for which it is not at all suited (big, corporate). So, we’re moving.

I’m curious as to what choices those are (?) Are you thinking of lo-code, RAD frameworks?

Anyway, manually creating the various admin related tables (using the SQLite script as the basis), has worked. For future reference, creating these tables (auth_user etc) with dbo as the owner works fine. However the ORM expects the Model tables to be qualified with the app name:

The above exception (('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'PollsterSystem_hpoterm'. (208) (SQLExecDirectW)")) was the direct cause of the following exception:

My one table at the moment is dbo.HPOTerm…but that clearly needs to be PollsterSystem.HPOTerm. This error occurred when I clicked on the link for the table admin form.

15

It’s actually dbo.PollsterSystem_HPOTerm.

I need to be careful when naming the application, I see. Oh well, this is a sandbox, so it’s no matter for now.

And by copying the records from the django_migrations table in the sqlite db to the sql server table, the outsanding migration messages vanish.

16

Oh, I’ve worked with all sorts and certainly wouldn’t sneer at any tech choice. I certainly see the wisdom in trying to reduce the size of a tech stack though, and doing away with some of the older techs is usually a good idea, simply because age often (not always) means it’s much harder to find good people to work with them. Many years back I wrote a regional bank reporting system mostly in Access - for all I know it’s still running :slight_smile:

I’m not sure what your better choices would be as I don’t know enough about your place. Perhaps there really aren’t any better choices given the things you are migrating to it.

For me though, the DB management and ORM are 2 of the really integral parts of Django, and to use django but try and work around either of them seems like it might bite you later. I’d rather be looking at tools where you rolled your own from the start, to avoid having to “fight” the framework (as you now seem to be having to do).

Perhaps this is a small price to pay for everything else though. To make any sensible suggestions I’d have to know a lot more about the work, the stack and the business requirements :slight_smile:

Anyway, I wish you luck in sorting this out.