###Use parameterized queries!
Use parameterized queries!
There are only very few situations where you would build SQL queries as strings then execute the strings as you are doing, and this is certainly not one of them. What you wrote is, for all intents and purposes, dynamic SQL, which very often should not be used, as it is inefficient and difficult to debug, in addition to potentially opening doors for injection.
Read: The Curse and Blessings of Dynamic SQL
An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2015年04月14日.
In your case, it appears that you already know what table and column(s) you will need ("The table is indexed by the column in the first select statement"), so just write parameterized statements accordingly. If you need different queries, just write new Python code for those as well. This will keep your calling code much cleaner and overall make your code more maintainable.
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = ?', (word_name))
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = ? WHERE YourWordColumn = ?', (new_freq, word_name))
Alternatively, use named parameters or another paramstyle:
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = :word', {"word": word_name})
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = :freq WHERE YourWordColumn = :word', {"freq": new_freq, "word": word_name})
###Use parameterized queries!
There are only very few situations where you would build SQL queries as strings then execute the strings as you are doing, and this is certainly not one of them. What you wrote is, for all intents and purposes, dynamic SQL, which very often should not be used, as it is inefficient and difficult to debug, in addition to potentially opening doors for injection.
Read: The Curse and Blessings of Dynamic SQL
An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2015年04月14日.
In your case, it appears that you already know what table and column(s) you will need ("The table is indexed by the column in the first select statement"), so just write parameterized statements accordingly. If you need different queries, just write new Python code for those as well. This will keep your calling code much cleaner and overall make your code more maintainable.
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = ?', (word_name))
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = ? WHERE YourWordColumn = ?', (new_freq, word_name))
Alternatively, use named parameters or another paramstyle:
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = :word', {"word": word_name})
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = :freq WHERE YourWordColumn = :word', {"freq": new_freq, "word": word_name})
Use parameterized queries!
There are only very few situations where you would build SQL queries as strings then execute the strings as you are doing, and this is certainly not one of them. What you wrote is, for all intents and purposes, dynamic SQL, which very often should not be used, as it is inefficient and difficult to debug, in addition to potentially opening doors for injection.
Read: The Curse and Blessings of Dynamic SQL
An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2015年04月14日.
In your case, it appears that you already know what table and column(s) you will need ("The table is indexed by the column in the first select statement"), so just write parameterized statements accordingly. If you need different queries, just write new Python code for those as well. This will keep your calling code much cleaner and overall make your code more maintainable.
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = ?', (word_name))
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = ? WHERE YourWordColumn = ?', (new_freq, word_name))
Alternatively, use named parameters or another paramstyle:
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = :word', {"word": word_name})
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = :freq WHERE YourWordColumn = :word', {"freq": new_freq, "word": word_name})
###Use parameterized queries!
There are only very few situations where you would build SQL queries as strings then execute the strings as you are doing, and this is certainly not one of them. What you wrote is, for all intents and purposes, dynamic SQL, which very often should not be used, as it is inefficient and difficult to debug, in addition to potentially opening doors for injection.
Read: The Curse and Blessings of Dynamic SQL
An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2015年04月14日.
In your case, it appears that you already know what table and column(s) you will need ("The table is indexed by the column in the first select statement"), so just write parameterized statements accordingly. If you need different queries, just write new Python code for those as well. This will keep your calling code much cleaner and overall make your code more maintainable.
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = ?', (word_name))
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = ? WHERE YourWordColumn = ?', (new_freq, word_name))
Alternatively, use named parameters or another paramstyle:
self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = :word', {"word": word_name})
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = :freq WHERE YourWordColumn = :word', {"freq": new_freq, "word": word_name})