Database data Integrity check using Python SqlAlchemy or Sql Query

Question

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

Python with SqlAlchemy and write the logic in python
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:

Possible combination use of Jupyter for nice user interface
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

Can all be managed in SQL server management studio

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
– Sam Hartman, Commented May 10, 2017 at 15:53

e4c5 · Accepted Answer · 2017-05-10 15:54:28Z

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.

answered May 10, 2017 at 15:54

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jim L Over a year ago

There is not a easy way to use "Constraints" to cross multiple tables. check this link. Also, during data insert, some logic will be broken, and will not apply.

e4c5 Over a year ago

That's not mentioned in your question. Anything that you do with your programming language is not going to fool proof.

Collectives™ on Stack Overflow

Database data Integrity check using Python SqlAlchemy or Sql Query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related