0

I imported and will keep importing data from different sources into sql server database. Some logic check such as sum of one column should be in 5 dollars difference with one amount in another table. It is hard for me to check this logic when importing because some data will be manually inserted, some imported using Excel VBA or python. Data Accuracy is very important to me. I am thinking to check after the data inserted. I am thinking two choices

  1. Python with SqlAlchemy and write the logic in python
  2. Create a stored procedure or direct SQL to verify

What will be advantages and disadvantages of SQLAlchemy vs stored procedure for data check? or other solutions?

The Benefit in my mind for SQLAlchemy with automap:

  1. Possible combination use of Jupyter for nice user interface
  2. Logic is easier to write, such as loop one table each row should be sum of another table with some where conditions.

Benefit for SQL stored procedure

  1. Can all be managed in SQL server management studio
Jim L
  • 3
  • 5
  • As the question is written, it's very close to being off-topic for stackoverflow because it focuses on what is our opinions. If you instead ask what the advantages and disadvantages of SQLAlchemy vs stored procedure for data checks, it would be a better question. It would be even better if you gave some examples of the checks you're thinking about. – Sam Hartman May 10 '17 at 15:53

1 Answers1

1

The answer is neither. Most RDBMS have built in mechanisms to enforce restrictions on the data that is inserted into a row or a column. As you said

It is hard for me to check this logic when importing because some data will be manually inserted, some imported using Excel VBA or python. Data Accuracy is very important to me

You can't have your code in all these places. What works is constraints.

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators

You can of course use stored procedures for this but constraints are more efficient, transparent and easier to maintain.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • There is not a easy way to use "Constraints" to cross multiple tables. check this [link](http://stackoverflow.com/questions/28172719/how-to-enforce-a-check-constraint-across-multiple-tables). Also, during data insert, some logic will be broken, and will not apply. – Jim L May 10 '17 at 19:12
  • That's not mentioned in your question. Anything that you do with your programming language is not going to fool proof. – e4c5 May 11 '17 at 02:35