Good day everyone! The situation is as follows: I wrote a script to work with mySQL database, the data in which is stored as utf8, the script works, but when inserting utf8 data that is taken from another table, stumbles about an error 1064 mySQL, I don’t understand the characters' \ xd0 \ xbd \ xd0 \ xb0 '. Here is the script itself:

import MySQLdb class mSQLDatabase: __host = 'localhost' __user = 'root' __passwd = '' __selectedDB = 'Database1' __receivedDB = 'Database2' __selectedconn = None __receivedconn = None def __init__(self): self.__selectedconn = MySQLdb.connect(self.__host,self.__user,self.__passwd,self.__selectedDB,use_unicode=True, charset='utf8') self.__receivedconn = MySQLdb.connect(self.__host,self.__user,self.__passwd,self.__receivedDB,use_unicode=True, charset='utf8') def insert(self, query): print(query) cursor = self.__receivedconn.cursor() cursor.execute(query) self.__receivedconn.commit() def query(self, query): cursor = self.__selectedconn.cursor(MySQLdb.cursors.DictCursor) cursor.execute(query) return cursor.fetchall() #!/usr/bin/env python import DatabaseClass import codecs db = DatabaseClass.mSQLDatabase() db = DatabaseClass.mSQLDatabase() query = db.query("select din from S_Din") myset = set() for row in query: myset.add(row.values()[0]) for name in myset: print(name) db.insert("INSERT INTO `Database2`.`discipline` (`name`) VALUES (%s)" %(name))
  • What version of python? - Arnial
  • @Arnial version 3.4 - Max
  • As far as I know, the MySQLdb extension does not support python3, and it will ever be unlikely. The project is dead. - Arnial
  • @Arnial it can be used if installed via pip. But, if you say that the project is dead, tell me that it is better to use to communicate with mysql. - Max
  • I am using PyMySQL (promises python support> = 3.3). It seems like there is still CyMySQL (an analogue of PyMySQL partially rewritten in Cython for acceleration), but I have not tried it. - Arnial

1 answer 1

In general, the solution is: instead

 db.insert("INSERT INTO `UniversityBeta`.`groups` (`groupn`) VALUES (%s)", %(name)) def insert(self, query): print(query) cursor = self.__receivedconn.cursor() cursor.execute(query) self.__receivedconn.commit()

use this:

 db.insert("INSERT INTO `UniversityBeta`.`groups` (`groupn`) VALUES (%s)",name) def insert(self, query, param): print(query) cursor = self.__receivedconn.cursor() cursor.execute(query,param) self.__receivedconn.commit()

This allows you to explicitly specify the data type when executing the query, which solves the problem of converting data between python and mysql.