Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Replace the table name in query with variabale value



  • Is it possible to replace the table name in query with variabale value?
    cb16c52d-5fe9-4e1a-9a4e-c680980a3161-image.png


  • Lifetime Qt Champion

    @dvlpr-bernard Sure:

    QString queryStrTemplate = "SELECT * from %1";
    c.execute(queryStrTemplate.arg("TABLE_NAME"));
    


  • @dvlpr-bernard
    Note that as @jsulm has written, for a table name (which is not shown as a variable in your code) you cannot do it via the SQL ?-binding mechanism, you must do it as he has shown by literal in-line substitution as you build the statement.


  • Banned

    Having had to work with this in multiple languages I tend to K.I.S.S. it. Which while similar to what @jsulm did is still slightly different as follows:

    SqlQuery = ''
    SqlQuery += 'SELECT FieldName '
    SqlQuery += '  FROM ' + tblName
    SqlQuery += ' WHERE FieldName = ' + FldVal
    
    DbCurs.execute(SqlQuery)
    RecordSet = DbCurs.fetchall()
    

    Note I prefer to do the fetchall because then I have a basic record set of the data I am going to be working with and this cuts down on the connection time to the back-end database in case more than one program is accessing it or might in the future. Also I use the += aspect to make adding new stuff a lot easier and to make all my SqlQuery assignments look/feel the same and it makes it a little bit easier to read the actual query code because it all lines up nicely



  • @jsulm
    Sir I was using PYQT and it displays an error.

    c4a2f88e-dc81-4f7a-b94a-32cebce86aca-image.png
    94e06239-d5bc-435a-83e3-b0a741e0c9f1-image.png


  • Lifetime Qt Champion

    @dvlpr-bernard You're using a Python string. So, simply use what Python provides you:

    queryStrTemplate = 'SELECT * from {}'
    c.execute(queryStrTemplate.format(table_name))
    


  • @dvlpr-bernard
    In addition to what @jsulm has corrected, once you get past that line you will need to correct your Python code when it gets to your subsequent line (oh, please try to paste code not screenshots, it makes it impossible for respondents to copy/paste!)

    c.execute('... {} \'.format(...) where SCHED_ID = ?', (1,)))
    

    It might help if you read up on Python strings.

    I also observe that since you are choosing to do all your database access through Python library calls, there is not a single bit of Qt or PyQt in the code you have now pasted. That's fine, but then for generic Python questions you might find a Python forum more suitable than a Qt one.



  • Thank you for your response :)
    But, I'm still getting an error. I apologize, I'm not familiar with the syntax of python.

    table_name = '1.0'
    queryStrTemplate = 'SELECT NO_EXERCISE from {}'
    c.execute(queryStrTemplate.format(table_name))
    
    c.execute('select NO_EXERCISE from {} \'.format(table_name) where SCHED_ID = ?', (1,))
    row = c.fetchone()
    print(row[0])
    

    36612b8b-8c2d-4a1e-bc69-fbafc0f11068-image.png


  • Lifetime Qt Champion

    @dvlpr-bernard said in Replace the table name in query with variabale value:

    I'm not familiar with the syntax of python

    Then you should learn it.
    And please take a closer look at your execute() line it is really broken...

    c.execute('select NO_EXERCISE from {} where SCHED_ID = {}'.format(table_name, 1))
    

    See https://www.geeksforgeeks.org/python-format-function/



  • @dvlpr-bernard said in Replace the table name in query with variabale value:

    But, I'm still getting an error. I apologize, I'm not familiar with the syntax of python.

    We (I) do not want to be rude or mean to you. But if you are not familiar with Python or its syntax, why are you programming in it, how do you expect to get anywhere? You are going to get this kind of issue on every line you try to write. Which is also why I said you might want to use a general Python programming forum, since this sort of thing simply has nothing to do with Qt, but as you please.



  • This post is deleted!


  • I didn't meant to offend you guys. I was working on a PyQt app it is just a small school project. Python is the required language, what do I mean is I don't have time to dig deeper on learning other python stuff. I learned the fundamentals and I also interested on the things that I needed. But in this case, I was hyped because I was about to finish my project. The only thing left is replacing the table name in query with variable value and then my project will get finished. That's maybe the reason why I was hyped to get the correct syntax for that.

    I apologize if I said something mean, English is not my first language. I hope you understand. Thank you for both of you :)



  • @dvlpr-bernard
    I finally noticed the problem. It must be

    table_name = "\'1.0\'"
    

    not

    table_name = '1.0'
    

    Thank you guys for your help :)


  • Banned

    It this for this reason (and a few others) that when developing a database that you do not use those kind of table names as they will end up being problematic -- also I believe I have tried to help you address how to normalize your database which would also facilitate not creating tables named as such -- but that is going to be your headache if you continue with this methodology

    Also PyQt is called that because it is Qt for Python which means to use it you must understand Python first -- Qt btw is originally written in C++ which is why there is PyQt (which goes by a few names actually) -- so in short as others have said to use PyQt you MUST learn Python

    Lastly I do not know about the others but I have found nothing offensive with your posts



  • @dvlpr-bernard
    One thing I meant to ask: what editor do you use when writing your Python code? If it's an IDE like PyCharm or VSCode it will helpfully show you when code is syntactically wrong, offer you methods/parameters to choose from etc.; if you are not using an editor with Python syntax you are missing out.


  • Banned

    @JonB I disagree ;-) but then I tend to be rather old school



  • @Denni-0 Thank you I will take note of that



  • @JonB I actually using IDLE hahaha. I do have PyCharm but I got used to the IDLE environment. Prob would gonna try them.



  • @dvlpr-bernard
    I imagine IDLE, being a Python editor, shows you errors, no? The point was, in your earlier erroneous code, I would have expected your editor to be showing you those lines were incorrect.



  • @Denni-0
    You would actively choose a generic editor without Python syntax/library knowledge over one which corrects, completes and so on? And you would recommend that to a self-proclaimed beginner?



  • @JonB HAHAHAHAHAH it works fine to me, IDLE also shows where the error is, but not as powerful as other IDE's can do. It would have been a little bit easier if I use other IDE's like PyCharm. But just like I what I have said I had so much fun and excitement using IDLE. Next time I would prob use PyCharm to put myself at ease.


  • Banned

    @JonB To answer your questions in order -- Yes I do and no I would not -- I did smile a wink did you miss that



  • @Denni-0
    No problem. I am surprised you choose a non-Python-environment editor, but that is your choice. :) I am also glad you are happy for the OP or a beginner to use the help he can get from one.


  • Banned

    @JonB like I said I am old school -- further I found that the IDEs for Python were a bit flaky (not extremely so but just enough of an annoyance to me to make me forego them) But I also have been coding for a rather long time often within minimal environments where having an IDE was not always possible -- as such I have gotten pretty used to (to be read as I code faster) using just Notepad++ to do my coding coupled with a web-page reference to source documentation


  • Banned

    @dvlpr-bernard what happened to your post about getting help with your database design -- I was going to make an update to it but I cannot seem to find it -- did you delete that one? I would have contacted you directly but cannot figure out how to do that on this forum or even if its possible



  • @Denni-0 I'm sorry sir. I felt bad about the replies(they were indirectly telling me that I don't belong or I shouldn't be here), so I decided to delete it. Forget about it, I appreciate your help sir :).


  • Banned

    Okay well I am not sure which comments you are referring to but you definitely belong here as do any and all folks looking for help with programming as I believe that is what the whole purpose of this forum -- which btw is a lot better than stackoverflow which has gone done hill greatly since its earlier days to the point it is not really worth posting questions there any more.

    Also if you ever feel you are not wanted around here speak up -- as I am sure there are others that feel the way I do and they would chime in.

    As for that issue if you can undelete it then I can reply to it otherwise you might need to repost the question so I can address it for you (and any others who might be interested in knowing that as well)

    Addendum: Oh wait I think I recall some of those comments and I think you took them wrong -- when folks said you ought to learn python it was not to say you do not belong here it was to say you would have a much easier time doing what you are trying to do if you learned python -- aka they were just pointing out part of the solution to the overall problem


  • Moderators

    @dvlpr-bernard said in Replace the table name in query with variabale value:

    I felt bad about the replies(they were indirectly telling me that I don't belong or I shouldn't be here)

    Hi @dvlpr-bernard, the Qt Forum is a place which welcomes everybody who wants to learn how to write software using Qt. If anyone says that you shouldn't be here, please let the Moderators know (click the triple-dots at the bottom-right of the post, and select "Flag this post for moderation") because we want to build a helpful and professional environment.

    Anyway, I reviewed the replies in your other thread (Moderators can see deleted posts). I agree with @Denni-0 -- None of the replies were (directly or indirectly) saying that you don't belong here. Instead, they were saying that you should redesign your SQL database schema in order to solve your problem.

    @Denni-0 said in Replace the table name in query with variabale value:

    I would have contacted you directly but cannot figure out how to do that on this forum or even if its possible

    You can send a Private Message from the user's profile page. Click the triple-dots and select "Start a new chat with <USERNAME>".


Log in to reply