Bash db function into python script-CodePudding

I have a problem that, in the bash profile, I was doing select 2 columns from a table and filtering all data which the framework column value is java into testcases file. But I want to use that python, I tried a lot of ways but without any success.

echo "select class, testcase from test_cases where framework = 'Java';" | mysql -h vm.dev.midasplayer.com --ssl-mode=DISABLED -u ${DB_USER} -p${DB_PASSWORD} tabletest > testcases

I try using the following python to connect the DB but then how to perform the SQL query and then import it into a file? Any help here will be much appreciated

import mysql.connector

mydb = mysql.connector.connect(
  host="vm.dev.midasplayer.com",
  user="username",
  password="password"
)

print(mydb)

CodePudding user response：

What you need is the most basic and elementary for backend developers who start to use a relational database. You wil find a lot if information in the internet with these google searches:

simple mysql crud with [java, python, c#, nodejs, etc]
simple mysql select with [java, python, c#, nodejs, etc]

Also you can add at the end of google search the following commands to get pdfs or ppts used in thousands of courses of all the world :)

simple mysql select with python filetype:ppt
simple mysql select with python filetype:pdf

Learning mode

If you have time to learn steps by step I recommend to read carefully all the information found with the recommended searches in the previous section

I share here some useful pdfs

If links in the future are offline, check this https://github.com/open-source-education/junior-software-developer/tree/main/jsd-306

If you don't have time to learn

Not recommended but if you don't have time to learn, you could use https://gist.github.com to search a code ready to use

https://gist.github.com/search?q=mysql select python&ref=searchresults

pre-requisites

pip install mysql-connector
pip install mysql-connector-python-rf

code

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='devopsschool1',
                                         user='root',
                                         password='MyNewPass')

    sql_select_Query = "select * from Laptop"
    cursor = connection.cursor()
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()
    print("Total number of rows in Laptop is: ", cursor.rowcount)

    print("\nPrinting each laptop record")
    for row in records:
        print("Id = ", row[0], )
        print("Name = ", row[1])
        print("Price  = ", row[2])
        print("Purchase date  = ", row[3], "\n")

except Error as e:
    print("Error reading data from MySQL table", e)
finally:
    if (connection.is_connected()):
        connection.close()
        cursor.close()
        print("MySQL connection is closed")

Snippet source: https://gist.github.com/devops-school/eab2f34e05145b9227ba413b1aa42da7#file-read-py