LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 05-15-2021, 02:15 PM   #1
Steve R.
Member
 
Registered: Jun 2009
Location: Morehead City, NC
Distribution: Mint 20.1
Posts: 491

Rep: Reputation: 92
Using a Python Variable in a MariaDB SQL Statement


I am using Linux Mint 20.1. I have successfully connected to a MariaDB with Python 3.85.

What I would like to achieve is to use a Python variable in an SQL statement such as as "SELECT" and/or "DELETE". The only samples that I have located so far with the "WHERE" clause where hard-coded examples.

Below is an (unsuccessful) example of what I wish to achieve. The variable "record_filter" will not be limited to one value as shown in the example below.

Code:
record_filter = "3900"

# Execute SQL statements
cur.execute("SELECT AuthorIDNUM, AuthorFirst FROM tblAuthorListX WHERE AuthorIDNUM > record_filter")
See updates below.

Last edited by Steve R.; 05-15-2021 at 04:52 PM.
 
Old 05-15-2021, 03:41 PM   #2
Steve R.
Member
 
Registered: Jun 2009
Location: Morehead City, NC
Distribution: Mint 20.1
Posts: 491

Original Poster
Rep: Reputation: 92
Of course, after posting, I finally located a solution. Still need to think about it as the number "3999" won't always be the same. "record_filter" in this situation, is being established as a tuple, which can't be changed. So that presents a "new" problem which I will have to contemplate. Need to look into "qmark style". This led me to: paramstyle. Which in turn led me to: pyformat.

Code:
record_filter = ["3999,"]

# Execute SQL statements
cur.execute("SELECT AuthorIDNUM, AuthorFirst FROM tblAuthorListX WHERE AuthorIDNUM = (?)",record_filter)
The solution to the above is located in: SQL Statements. Scroll down to the text: "import sqlite3".

Last edited by Steve R.; 05-15-2021 at 04:54 PM.
 
Old 05-15-2021, 04:51 PM   #3
Steve R.
Member
 
Registered: Jun 2009
Location: Morehead City, NC
Distribution: Mint 20.1
Posts: 491

Original Poster
Rep: Reputation: 92
Progress. I experimented with using the "pyformat". Which appears to let me use a single value for "record_filter", that I should be able to programmatically change. Any good tutorials on using "pyformat"?

Code:
record_filter = 3990

# Execute SQL statements
cur.execute("SELECT AuthorIDNUM, AuthorFirst FROM tblAuthorListX WHERE AuthorIDNUM >= %(susan)s", {'susan': record_filter})
Solution above adapted from: Python best practice and securest to connect to MySQL and execute queries

Last edited by Steve R.; 05-15-2021 at 04:55 PM.
 
Old 05-15-2021, 05:11 PM   #4
mimorek
Member
 
Registered: Feb 2013
Distribution: Debian (jessie)
Posts: 42

Rep: Reputation: Disabled
I've have a little experience using sqlite3.

Quote:
The Python string operator % must not be used. The execute() method accepts a tuple or list as second parameter.
Read here

The advised way:
Code:
record_filter = (3990,)

# Execute SQL statements
cur.execute("SELECT AuthorIDNUM, AuthorFirst FROM tblAuthorListX WHERE AuthorIDNUM >= (?)", record_filter)

or:

cur.execute("SELECT AuthorIDNUM, AuthorFirst FROM tblAuthorListX WHERE AuthorIDNUM >= (?)", (3990,) )
Official documentation: mariadb-connector-python

PEP 249 -- Python Database API Specification v2.0


Quote:
Originally Posted by Steve R. View Post
That question concerns the MySQLdb api.

Last edited by mimorek; 05-15-2021 at 05:31 PM.
 
1 members found this post helpful.
Old 05-15-2021, 06:57 PM   #5
Steve R.
Member
 
Registered: Jun 2009
Location: Morehead City, NC
Distribution: Mint 20.1
Posts: 491

Original Poster
Rep: Reputation: 92
Thank-you for responding. It will take me a while to digest this information.
 
Old 05-16-2021, 07:19 AM   #6
Steve R.
Member
 
Registered: Jun 2009
Location: Morehead City, NC
Distribution: Mint 20.1
Posts: 491

Original Poster
Rep: Reputation: 92
Both these constructs worked. Used the "#" symbol to toggle between the two options.
Code:
delete_record = AuthorIDNUM
#delete_cursor.execute("DELETE FROM tblAuthorListX WHERE AuthorIDNUM = %(susan)s", {'susan': delete_record}) #worked!
delete_cursor.execute("DELETE FROM tblAuthorListX WHERE AuthorIDNUM = (?)", (delete_record,)) #Worked!
My SQL statements were "nested", so two additional tweaks had to be applied to resolve error situations.
Code:
cur = conn.cursor(buffered=True)
delete_cursor = conn.cursor(buffered=True)
Needed to establish a second recordset (delete_cursor), which then created a need to have both recordsets buffered (conn.cursor(buffered=True)).

It was a tremendous Python learning experience and integrating Python with MariaDB.

Last edited by Steve R.; 05-16-2021 at 07:31 AM.
 
Old 05-16-2021, 12:50 PM   #7
mimorek
Member
 
Registered: Feb 2013
Distribution: Debian (jessie)
Posts: 42

Rep: Reputation: Disabled
Quote:
Originally Posted by Steve R. View Post
Both these constructs worked. Used the "#" symbol to toggle between the two options.
Code:
delete_record = AuthorIDNUM
#delete_cursor.execute("DELETE FROM tblAuthorListX WHERE AuthorIDNUM = %(susan)s", {'susan': delete_record}) #worked!
delete_cursor.execute("DELETE FROM tblAuthorListX WHERE AuthorIDNUM = (?)", (delete_record,)) #Worked!
You are right when you say they work.
I just read in the MariaDB manual that they prefer/advice the second method. I don't know why.
Quote:
Passing parameters to SQL statements

As shown in previous example, passing parameters to SQL statements happens by using placeholders in the statement. By default MariaDB Connector/Python uses a question mark as a placeholder, for compatibility reason also %s placeholders are supported.
Also:
Quote:
delete_record = AuthorIDNUM
delete_cursor.execute("DELETE FROM tblAuthorListX WHERE AuthorIDNUM = %(susan)s", {'susan': delete_record}) #worked!
That makes use of a dictionary.
Code:
my_dict = {
    'col1' : 12345,
    'col2' : 67890,
    'col3' : "abcde" }

cursor.execute("INSERT INTO table (column1, column2, column3) VALUES ( %(col1)s, %(col2)s, %(col3)s )", my_dict )

Last edited by mimorek; 05-16-2021 at 02:01 PM.
 
1 members found this post helpful.
  


Reply

Tags
mariadb, python3


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: How to combine the results of multiple SQL queries using the UNION statement LXer Syndicated Linux News 0 04-29-2019 01:02 AM
LXer: KDE Participating in Google Summer of Code 2019, MariaDB Releasing New Open-Source MariaDB Enterprise Server, CentOS Celebrates 15th B LXer Syndicated Linux News 0 02-28-2019 06:50 AM
SQL update statement to SQL SERVER 2005 scheidel21 Programming 2 11-05-2009 06:30 PM
LXer: Python Python Python (aka Python 3) LXer Syndicated Linux News 0 08-05-2009 08:30 PM
AWK a variable Ouptut to a new variable and using the new variable with the old one alertroshannow Linux - Newbie 4 02-16-2009 12:08 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 01:36 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration