inserting into a database (mysql) via a python program

Question

i am familiar with python and also familiar with mysql and SQL. I am also clear on con, cur and commit, but my problem here is that im trying to make a small program in python (no need for a gui) to insert data into a mysql database, and bring it on my console or file. I'm confused where to start, i tried seeking google but i couldn't find any toturials about my issue, any help? a link or where to start. Also:

i know the python program can be written in an IDE or a text file, but how does it connect to mysql database? if im wrong please correct me.

Try searching with text *python program to get data from database* searching *Google* by itself is a good skill — Rahul, Mar 23 '17 at 19:27
i did excatly that and i didn't get anything, and the important thing here is how to write to a database, this is what i don't get, i do know we're gonna use sql inside the program, but i dont understand how to connect to the mysql database — Dr. MaxCox, Mar 23 '17 at 19:30
Use the mysqldb python library and read the documentation here: http://mysql-python.sourceforge.net/MySQLdb.html — lordingtar, Mar 23 '17 at 19:32

score 0 · Answer 1 · answered Mar 23 '17 at 19:32

0

SQLAlchemy is good: https://www.sqlalchemy.org/

Otherwise, using the conn/cur as you described is easy: https://www.tutorialspoint.com/python/python_database_access.htm

answered Mar 23 '17 at 19:32

Artagel

390
1
12

score 0 · Answer 2 · answered Mar 23 '17 at 19:32

Go though the documentation to get yourself familiar with python, mysql and how to work with them together.

Although, the minimal code would look something like this :

import MySQLdb

query = "insert into DB_NAME values (1,2)"

try : 
    conn = MySQLdb.connect(host="",
        user="",
        passwd="",
        db="")
    cursor = conn.cursor()
    cursor.execute(query)
    conn.commit()
    cursor.close()
    conn.close()
except  (MySQLdb.Error, Exception) as error :
    print error
    print "Insert data unsuccessful"

thanks guys, i got it to work! i used pymysql instead of mysqldb because it is easier and has less issues. — Dr. MaxCox, Apr 02 '17 at 13:13

score 0 · Answer 3 · edited Mar 23 '17 at 21:05

See the code below

import mysql.connector

from mysql.connector import MySQLConnection, Error

class SQL_Connect:

def __init__(self):
    #-------------------------------------------------------
    # Database Connection Param's
    self.host_Address = 'Host Here'
    self.database_Name = 'Database Name'
    self.userName = 'User Name'
    self.db_Password = 'Password'
    #-------------------------------------------------------


def insert_IntoDB(self, Manufacturer, partNum, formFactor, socket, chipSet, memSlots, memType, maxMem, raidSup, onboardVid, crosFire_Sup, sli_Sup, sata6GBS, sataExpress, onboard_Ether):
    test_Query = 'INSERT INTO motherboards (Manufacturer, modelNum, formFactor, socket, chipset, memSlots, memType, maxMem, raidSup, onboardVid, crosfireSup, sliSup, sata6GBS, sataExpress, onboardEther) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    args = (Manufacturer, partNum, formFactor, socket, chipSet, memSlots, memType, maxMem, raidSup, onboardVid, crosFire_Sup, sli_Sup, sata6GBS, sataExpress, onboard_Ether)
    try:
        conn = mysql.connector.connect(host = self.host_Address, database = self.database_Name, user = self.userName, password = self.db_Password)
        if conn.is_connected():
            print 'MySQL Database Connection Established'
        cursor = conn.cursor()
        cursor.execute(test_Query, args)

        conn.commit()
        print 'Data Inserted!!!'

    except Error as e:
        print ('ERROR: ',e)

    finally:
        cursor.close()
        conn.close()

inserting into a database (mysql) via a python program

3 Answers3