CODEWITHSUNDEEP
pythonmysqlsql-insert
Omar
Omar

Reputation: 61

MySQL INSERT statement in Python

I am trying to use Python to insert into MySQL database, but I have an auto-increment column (TeamID). I am using the lines below and it works like a charm. BUT I would like to not specify the TeamID in my Python script as it is an auto-increment

try:

cur.execute ("INSERT INTO teams values (%d, '%s', %d, '%s')" % (11,"Sevilla", 7, "Jorge Sampaoli"))
db.commit()

this works perfectly

How can I get rid of the first %d and 11 please? I want this value to be added automatically via the script

any help is very much appreciated

EDIT:

#!/usr/bin/python 
import MySQLdb 
db = MySQLdb.connect(host="localhost", # your host, usually localhost  
    user="username", # your username 
    passwd="password", # your password 
    db="dbname") # name of the data base 
cur = db.cursor() 
try: 
    cur.execute ("INSERT INTO teams values ('%s', %d, '%s')" % ("Sevilla", 7, "Jorge Sampaoli")) 
    db.commit() 
except Exception as e: 
    print("Rolling back") 
    print(e) 
    db.rollback() 
db.close()

Upvotes: 0

Views: 6656

Answers (3)

24_saurabh sharma
24_saurabh sharma

Reputation: 201

import sqlite3
def insert_data(lVideoList, gate_id):
    connection = sqlite3.connect('db.sqlite',
                                 detect_types=sqlite3.PARSE_DECLTYPES |
                                              sqlite3.PARSE_COLNAMES)
    cursor = connection.cursor()
    success = 200
    # gateid = 1
    default_value = 0
    for gate_id in range(no_of_gate):
        gate_id = i+1
        for videofilename in lVideoList:
            print("videofilename: ", videofilename)
            insert_query = ("INSERT INTO dailyfootfall(csv_name, video_download, processed, footfall, send_status, "
                            "male_footfall, send_status_male, female_footfall, send_status_female, gate_id,outsiders, send_status_outsiders) "
                            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)")
            
            cursor.execute(insert_query,[videofilename, success, success, default_value, success, default_value,
                                        success, default_value, success, gate_id, default_value, success])

       
    print("Data Inserted Successfully !")
    connection.commit()
    cursor.close()
    connection.close()

if __name__ == "__main__":

    lVideoList = getCompleteVideoList("2022_01_24", "10:00", "22:00")
    no_of_gate = 3
    insert_data (lVideoList, gate_id)
    print("default_value inserted!!!!")

Upvotes: 0

Omar
Omar

Reputation: 61

Issue is now resolved

I did specify the column names but didn't notice I need to use %s for all columns including int values. As below:

cur.execute("INSERT INTO teams (TeamName, CountryID, TeamManager) values (%s,%s,%s)", ('Everton', 1, 'Ronald Koeman'))

Upvotes: 1

MK.
MK.

Reputation: 34517

Try 

INSERT INTO teams (name, numb, player) VALUES ('%s', %d, '%s')

I.e.explicitly list columns. Also PLEASE don't do it like this -- instead of doing '%s' you really need to use prepared statements,I think in Python it is something like:

cursor.execute("INSERT INTO teams (name, numb, player) VALUES (%s, %d, %s)", ['Sevilla', 7, 'John Smith'])

Read up on SQL injections.

Upvotes: 0

Related Questions