Programing

MySQL 데이터베이스에 데이터를 삽입하려면 어떻게해야합니까?

lottogame 2020. 12. 14. 07:44
반응형

MySQL 데이터베이스에 데이터를 삽입하려면 어떻게해야합니까?


MySQL 데이터베이스에 정수 188과 90을 삽입하고 싶지만 다음 코드가 작동하지 않습니다.

import MySQLdb
conn = MySQLdb.connect(host= "localhost",
                  user="root",
                  passwd="newpassword",
                  db="engy1")
x = conn.cursor()
x.execute("SELECT *  FROM anooog1")
x.execute (" INSERT INTO anooog1 VALUES ('%s','%s') ", (188,90))
row = x.fetchall()

왜 작동하지 않습니까?


#Server Connection to MySQL:

import MySQLdb
conn = MySQLdb.connect(host= "localhost",
                  user="root",
                  passwd="newpassword",
                  db="engy1")
x = conn.cursor()

try:
   x.execute("""INSERT INTO anooog1 VALUES (%s,%s)""",(188,90))
   conn.commit()
except:
   conn.rollback()

conn.close()

나를 위해 일하는 편집 :

>>> import MySQLdb
>>> #connect to db
... db = MySQLdb.connect("localhost","root","password","testdb" )
>>> 
>>> #setup cursor
... cursor = db.cursor()
>>> 
>>> #create anooog1 table
... cursor.execute("DROP TABLE IF EXISTS anooog1")
__main__:2: Warning: Unknown table 'anooog1'
0L
>>> 
>>> sql = """CREATE TABLE anooog1 (
...          COL1 INT,  
...          COL2 INT )"""
>>> cursor.execute(sql)
0L
>>> 
>>> #insert to table
... try:
...     cursor.execute("""INSERT INTO anooog1 VALUES (%s,%s)""",(188,90))
...     db.commit()
... except:     
...     db.rollback()
... 
1L
>>> #show table
... cursor.execute("""SELECT * FROM anooog1;""")
1L
>>> print cursor.fetchall()
((188L, 90L),)
>>> 
>>> db.close()

mysql의 테이블;

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM anooog1;
+------+------+
| COL1 | COL2 |
+------+------+
|  188 |   90 |
+------+------+
1 row in set (0.00 sec)

mysql> 

다음은 OOP입니다.

import MySQLdb


class Database:

    host = 'localhost'
    user = 'root'
    password = '123'
    db = 'test'

    def __init__(self):
        self.connection = MySQLdb.connect(self.host, self.user, self.password, self.db)
        self.cursor = self.connection.cursor()

    def insert(self, query):
        try:
            self.cursor.execute(query)
            self.connection.commit()
        except:
            self.connection.rollback()



    def query(self, query):
        cursor = self.connection.cursor( MySQLdb.cursors.DictCursor )
        cursor.execute(query)

        return cursor.fetchall()

    def __del__(self):
        self.connection.close()


if __name__ == "__main__":

    db = Database()

    #CleanUp Operation
    del_query = "DELETE FROM basic_python_database"
    db.insert(del_query)

    # Data Insert into the table
    query = """
        INSERT INTO basic_python_database
        (`name`, `age`)
        VALUES
        ('Mike', 21),
        ('Michael', 21),
        ('Imran', 21)
        """

    # db.query(query)
    db.insert(query)

    # Data retrieved from the table
    select_query = """
        SELECT * FROM basic_python_database
        WHERE age = 21
        """

    people = db.query(select_query)

    for person in people:
        print "Found %s " % person['name']

참고 URL : https://stackoverflow.com/questions/5687718/how-can-i-insert-data-into-a-mysql-database

반응형