CODEWITHSUNDEEP
pythonmariadb
stories2
stories2

Reputation: 486

MariaDB can't insert data

A database user can connect to the server from another place. They have access permission. The server firewall is disabled. 

import pymysql
def ConnectToDatabase():
    return pymysql.connect(host = "hostName", user = "databaseUserId", password = "databaseUserPw", db = "databaseName", charset = "utf8")

def DisconnectDatabase(databaseConnection) :
    databaseConnection.close()

def ExecuteQueryToDatabase(executeAbleQuery) :
    databaseConnection = ConnectToDatabase()
    databaseResultDataCursor = databaseConnection.cursor()
    databaseResultDataCursor.execute(executeAbleQuery)
    databaseResultDataRows = databaseResultDataCursor.fetchall()
    DisconnectDatabase(databaseConnection)
    return databaseResultDataRows

def ClientRequestQuery(request) :
    dbQuery = request.GET.get('query',';')
    print dbQuery
    return HttpResponse(ExecuteQueryToDatabase(dbQuery))

def TestQuery(request):

    ExecuteQueryToDatabase('insert into User2 (`a`, `b`, `c`, `d`) values(1, 2, 3, 4);');

    return HttpResponse("test")

The code is just ti connect to MariaDB and insert some data, but when I try this code, the data is gone with no error and "test" is returned successfully

The table structure is

    MariaDB [ServiceDatabase]> desc User2;
│+-------+---------+------+-----+---------+-------+

│| Field | Type    | Null | Key | Default | Extra |

│+-------+---------+------+-----+---------+-------+

│| a     | int(11) | NO   |     | NULL    |       |

│| b     | int(11) | NO   |     | NULL    |       |

│| c     | int(11) | NO   |     | NULL    |       |

│| d     | int(11) | NO   |     | NULL    |       |

│+-------+---------+------+-----+---------+-------+

│4 rows in set (0.00 sec)

The Django version is 10.0.2 The Python version is 2.7.12

Can anyone help me?

Upvotes: 2

Views: 1483

Answers (2)

John
John

Reputation: 1759

Use the mariadb connector and set autocommit=True like

# Module Import
import mariadb

# Instantiate Connection
try:
conn = mariadb.connect(
    user="john",
    password="secret",
    host="localhost",
    port=3306,
    autocommit=True)
    
except mariadb.Error as e:
print(f"Error connecting to MariaDB Platform: {e}")
sys.exit(1)

Upvotes: 1

Christian W.
Christian W.

Reputation: 2660

You are not performing commit at any point in your code. The general way to execute inserts on databases with python is to

1) Create connection and cursor

 conn = MySQLdb.connect(host= "hostname",
              user="root",
              passwd="password",
              db="dbname")
 x = conn.cursor()

2) execute insert and then commit or rollback if transaction fails

try:
    x.execute("INSERT INTO User2 (a, b, c, d) VALUES (%s, %s, %s, %s)", (1,2,3,4))
    conn.commit()
except:
    conn.rollback()
conn.close()

As far as I can see, you are never performing commit(), so the transaction is actually never completed.

Upvotes: 1

Related Questions