MySQL INSERT statement in Python

Question

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()

#!/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() — Omar
– Omar, Commented Feb 15, 2017 at 15:22

Ilja Everilä · Accepted Answer · 2018-04-17 16:37:17Z

1

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'))

edited Apr 17, 2018 at 16:37

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

answered Feb 16, 2017 at 10:07

Omar

612 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MK. · Accepted Answer · 2017-02-15 16:32:56Z

0

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.

answered Feb 15, 2017 at 16:32

MK.

34.8k19 gold badges79 silver badges117 bronze badges

Comments

Flair · Accepted Answer · 2022-01-29 07:03:12Z

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!!!!")

Collectives™ on Stack Overflow

MySQL INSERT statement in Python

this works perfectly

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

this works perfectly

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related