Hi çağrı thanks for your question! Not sure what your SQLAlchemy models look like, but if you have a table that represents blog posts and another table that represents comments, and if you have a relationship between the two, getting the count of comments for a post would be as simple as "post.comments.count()". Then return a dictionary in your route, making sure the response validation model is set to bloglistmodel. Not sure if this helps - if you have some code that I can look at I may be able to give a more accurate answer.
Hi Mark absolutely! You can connect to the database directly using the driver. For pyodbc + SQL Server here's a nice tutorial: learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver16 When you connect with the dirver, you'll be running SQL statements directly against the db and you'll get row objects from each execution. Only thing I need to advise is make sure you parametrize your queries to avoid SQL injection. Unfortunately it's a very common mistake to run unparametrized queries. The tutorial I linked above contains examples of parametrized queries. When using pyodbc, you connect to the database once. pyodbc's cursor is similar to SQLAlchemy's cursor - you get a cursor every time you need to perform a new operation with the db. To avoid having to manage the cursor yourself, use the context manager (github.com/mkleehammer/pyodbc/wiki/Cursor#context-manager) - "with connection.cursor() as cursor". Check out also the documentation about managing transactions with pyodbc (github.com/mkleehammer/pyodbc/wiki/Database-Transaction-Management). Hope this helps!
Thank you so much for this tutorial. I have one question about the UUID implementation. Does this only work with certain databases or all SQL databases? I am running MySQL, I implemented it exactly like you did but instead of running a migration I write out my schema by hand. It places the UUID in the response body but when I refresh the page it does not generate a new UUID it only produces a duplicate UUID.
Hi Nick thanks for your question! Once you create a task with POST, SQLAlchemy should generate the UUID and save it to the database, and it shouldn't change. Are you seeing a different behaviour?
@@microapis You very welcome, this was a great tutorial. I found where I was making the mistake. When creating our post request in api.py I was not writing out the key value pairs(lines 25-28). I was trying to short cut it by posting it as whole object. It adds the UUID from the sqlalchemy model but since there is no function call on the post request it never updates the UUID. It only runs as a single instance.
Hi Ma that's a great point actually! A lot of the time I see companies exposing resources with numerical IDs, which opens the door for malicious users to play around with numbers and try to get access to resources that don't belong to them. Thanks for highlighting it!
Jose, thank you soo much for the content of this series. I will definitely get your book. Again great job!
Thank you for your kind feedback Walter!
excellent video! I will definitely use this information on my projects, thanks so much :)
Thank you so much for your nice feedback! It's very encouraging to hear that you find the information useful!
I have question i create a schema whic is name bloglistmodel and it has prop comment count and i want to count 1 blog tables comments how can i do
Hi çağrı thanks for your question! Not sure what your SQLAlchemy models look like, but if you have a table that represents blog posts and another table that represents comments, and if you have a relationship between the two, getting the count of comments for a post would be as simple as "post.comments.count()". Then return a dictionary in your route, making sure the response validation model is set to bloglistmodel. Not sure if this helps - if you have some code that I can look at I may be able to give a more accurate answer.
Can I connect to DB without using SQLAlchemy?. For eg, I want connec to SQL Server using pyodbc
Hi Mark absolutely! You can connect to the database directly using the driver. For pyodbc + SQL Server here's a nice tutorial: learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver16
When you connect with the dirver, you'll be running SQL statements directly against the db and you'll get row objects from each execution. Only thing I need to advise is make sure you parametrize your queries to avoid SQL injection. Unfortunately it's a very common mistake to run unparametrized queries. The tutorial I linked above contains examples of parametrized queries.
When using pyodbc, you connect to the database once. pyodbc's cursor is similar to SQLAlchemy's cursor - you get a cursor every time you need to perform a new operation with the db. To avoid having to manage the cursor yourself, use the context manager (github.com/mkleehammer/pyodbc/wiki/Cursor#context-manager) - "with connection.cursor() as cursor". Check out also the documentation about managing transactions with pyodbc (github.com/mkleehammer/pyodbc/wiki/Database-Transaction-Management).
Hope this helps!
Thank you so much for this tutorial. I have one question about the UUID implementation. Does this only work with certain databases or all SQL databases? I am running MySQL, I implemented it exactly like you did but instead of running a migration I write out my schema by hand. It places the UUID in the response body but when I refresh the page it does not generate a new UUID it only produces a duplicate UUID.
Hi Nick thanks for your question! Once you create a task with POST, SQLAlchemy should generate the UUID and save it to the database, and it shouldn't change. Are you seeing a different behaviour?
Forgot to say, thank you for checking the tutorial and for your nice feedback!!
@@microapis You very welcome, this was a great tutorial. I found where I was making the mistake. When creating our post request in api.py I was not writing out the key value pairs(lines 25-28). I was trying to short cut it by posting it as whole object. It adds the UUID from the sqlalchemy model but since there is no function call on the post request it never updates the UUID. It only runs as a single instance.
Hi, which theme u use for PyCharm in this video?
Hi Anton thanks for your question! I never thought too much about this so had to check haha. The theme I use is Dracula
@@microapis hi, thx )
tnx for this tutorial
My pleasure! Thanks for the nice comment!
You should precise the code is secure if you use an UUID for this id. If not your code is highly insecure. You should warn about it.
Hi Ma that's a great point actually! A lot of the time I see companies exposing resources with numerical IDs, which opens the door for malicious users to play around with numbers and try to get access to resources that don't belong to them. Thanks for highlighting it!
keep it up
Thanks for the nice feedback!