蟒蛇

Question

• 我正在运行Ubuntu 18.04。
  • 我使用mysql连接器-python连接Python到MySQL。
  • 我使用的是Python 3.6.7，并且已经安装了mysql连接器-python。
  • 我已经安装了mysql连接器-python-py3_8.0.13-1ubuntu18.10_all.deb.

  在运行Python脚本时，mysql。连接器模块似乎加载正确，但脚本在碰到光标时失败。next（）具有以下回溯错误：

  AttributeError:'CMySQLCursor'对象没有属性'next'

  有趣的是，当我使用Python2.7运行相同的python脚本时，它可以完美地工作。

  import mysql.connector
  import reddit_constants as rc
  import logging
  
  
  class MysqlManager:
      def __init__(self, database, username, password, logger):
          self.username = username
          self.password = password
          self.database = database
          self.logger = logger
  
      def __enter__(self):
          try:
              self.create_db()
              self.conn = mysql.connector.connect(
                  user=self.username,
                  passwd=self.password,
                  auth_plugin='mysql_native_password',
                  database=self.database
              )
              if self.conn.is_connected():
                  self.logger.info('Connected to reddit database')
              return self
          except:
              self.logger.error('Error: Could not connect to reddit database')
              self.conn.close()
  
      def __exit__(self, exc_type, exc_val, exc_tb):
          self.conn.close()
  
      def create_db(self):
          mydb = mysql.connector.connect(
              user=self.username,
              passwd=self.password,
              auth_plugin='mysql_native_password'
          )
  
          cursor = mydb.cursor()
  
          cursor.execute("CREATE DATABASE IF NOT EXISTS " + self.database)
  
          mydb.close()
  
      def create_tables(self):
          if not self.conn.is_connected():
              self.logger.Error('Error in MySqlManager: You must initialize the connection to MySql')
              return
  
          cursor = self.conn.cursor()
          cursor.execute("""
              CREATE TABLE IF NOT EXISTS subreddit 
              (
                  subreddit_id INT AUTO_INCREMENT,
                  subreddit_name VARCHAR(30) NOT NULL UNIQUE,
                  subscriptions INT,
                  PRIMARY KEY (subreddit_id)
              )
          """)
  
          cursor.execute("""
              CREATE TABLE IF NOT EXISTS post (
                  post_id INT AUTO_INCREMENT,
                  subreddit_id INT NOT NULL,
                  post_title VARCHAR(500) NOT NULL,
                  post_ref VARCHAR(2084),
                  comments_ref VARCHAR(2084),
                  username VARCHAR(30),
                  created_time DATETIME NOT NULL,
                  PRIMARY KEY (post_id),
                  FOREIGN KEY (subreddit_id) REFERENCES subreddit(subreddit_id),
                  CONSTRAINT UC_post UNIQUE(subreddit_id, post_title, username, created_time)
              )
          """)
  
          cursor.execute("""
              CREATE TABLE IF NOT EXISTS post_history (
                  post_history_id INT AUTO_INCREMENT,
                  post_id INT,
                  votes INT,
                  ranks INT,
                  updated_time DATETIME,
                  PRIMARY KEY (post_history_id),
                  FOREIGN KEY (post_id) references post(post_id)
              )    
          """)
  
          cursor.execute("""
              CREATE TABLE IF NOT EXISTS comment 
              (
                  comment_id INT AUTO_INCREMENT,
                  post_id INT NOT NULL,
                  subreddit_id INT NOT NULL,
                  username VARCHAR(30) NOT NULL,
                  created_time DATETIME NOT NULL,
                  PRIMARY KEY (comment_id),
                  FOREIGN KEY (subreddit_id) REFERENCES subreddit(subreddit_id),
                  FOREIGN KEY (post_id) REFERENCES post(post_id),
                  CONSTRAINT UC_comment UNIQUE(subreddit_id, post_id, username, created_time)
              )
          """)
  
          cursor.execute("""
              CREATE TABLE IF NOT EXISTS comment_history 
              (
                  comment_history_id INT AUTO_INCREMENT,
                  comment_id INT,
                  message TEXT,
                  votes INT,
                  updated_time DATETIME,
                  PRIMARY KEY (comment_history_id),
                  FOREIGN KEY (comment_id) references comment(comment_id)
              )    
          """)
  
          cursor.execute("""
              CREATE TABLE IF NOT EXISTS youtube_info
              (
                  youtube_info_id INT AUTO_INCREMENT,
                  post_id INT,
                  video_title TEXT,
                  publish_date DATETIME,
                  view_count INT,
                  like_count INT,
                  dislike_count INT,
                  comment_count INT,
                  PRIMARY KEY (youtube_info_id),
                  FOREIGN KEY (post_id) REFERENCES post(post_id),
                  CONSTRAINT UC_youtube_info UNIQUE(post_id)
              )
          """)
  
      def insert_subreddits(self, posts):
          if not self.conn.is_connected():
              self.logger.Error('Error in MySqlManager: You must initialize the connection to MySql')
              return
          cursor = self.conn.cursor()
  
          for post in posts:
              values = (post[rc.SUBREDDIT_KEY], None)
              query = """
                  INSERT IGNORE INTO subreddit (subreddit_name, subscriptions)
                  VALUES(%s, %s)
              """
              cursor.execute(query, values)
              self.conn.commit()
              new_id = cursor.lastrowid
              if new_id == 0:
                  id_query = "SELECT subreddit_id FROM subreddit WHERE subreddit_name = %s"
                  id_values = (post[rc.SUBREDDIT_KEY],)
                  cursor.execute(id_query, id_values)
                  new_id = cursor.next()[0]
              post[rc.SUBREDDIT_ID] = new_id
          self.logger.info(' - Inserted subreddits from page successfully')
  
      def insert_posts(self, posts):
          if not self.conn.is_connected():
              self.logger.Error('Error in MySqlManager: You must initialize the connection to MySql')
              return
          cursor = self.conn.cursor()
  
          for post in posts:
              post_values = (post[rc.SUBREDDIT_ID],
                             post[rc.POST_TITLE_KEY],
                             post[rc.POST_REF_KEY],
                             post[rc.COMMENTS_REF_KEY],
                             post[rc.USER_KEY],
                             post[rc.CREATED_TIMESTAMP_KEY])
              post_query = """
                  INSERT IGNORE INTO post (subreddit_id, post_title, post_ref,
                  comments_ref, username, created_time)
                  VALUES (%s, %s, %s, %s, %s, %s)
              """
  
              cursor.execute(post_query, post_values)
              self.conn.commit()
              new_id = cursor.lastrowid
  
              if new_id == 0:
                  id_query = """
                      SELECT post_id 
                      FROM post 
                      WHERE subreddit_id = %s
                      AND post_title = %s
                      AND username = %s
                      AND created_time = %s
                  """
                  id_values = (post[rc.SUBREDDIT_ID],
                               post[rc.POST_TITLE_KEY],
                               post[rc.USER_KEY],
                               post[rc.CREATED_TIMESTAMP_KEY])
                  cursor.execute(id_query, id_values)
                  new_id = cursor.next()[0]
              post[rc.POST_ID] = new_id
  
              post_history_values = (post[rc.POST_ID],
                                     post[rc.VOTE_KEY],
                                     post[rc.RANK_KEY])
  
              post_history_query = """
                  INSERT INTO post_history (post_id, votes, ranks, updated_time) 
                  (SELECT %s, %s, %s, NOW())
              """
  
              cursor.execute(post_history_query, post_history_values)
              self.conn.commit()
          self.logger.info(' - Inserted posts from page successfully')
  
      def insert_video_info(self, video_info):
          if not self.conn.is_connected():
              self.logger.Error('MySqlManager: You must initialize the connection to MySql')
              return
          cursor = self.conn.cursor()
  
          video_info_values = (video_info[rc.POST_ID],
                               video_info[rc.YOUTUBE_TITLE_KEY],
                               video_info[rc.YOUTUBE_PUBLISHED_KEY],
                               video_info[rc.YOUTUBE_VIEW_COUNT_KEY],
                               video_info[rc.YOUTUBE_LIKE_KEY],
                               video_info[rc.YOUTUBE_DISLIKE_KEY],
                               video_info[rc.YOUTUBE_COMMENT_KEY])
  
          video_info_query = """
              INSERT IGNORE INTO youtube_info (post_id, 
                  video_title,
                  publish_date,
                  view_count,
                  like_count,
                  dislike_count,
                  comment_count)
              VALUES (%s, %s, %s, %s, %s, %s, %s)
          """
  
          cursor.execute(video_info_query, video_info_values)
          self.conn.commit()
  
      def insert_comments(self, comments, post):
          if not self.conn.is_connected():
              self.logger.Error('Error in MySqlManager: You must initialize the connection to MySql')
              return
          cursor = self.conn.cursor()
  
          for comment in comments:
  
              comment_values = (post[rc.POST_ID],
                                post[rc.SUBREDDIT_ID],
                                comment[rc.USER_KEY],
                                comment[rc.CREATED_TIMESTAMP_KEY])
              comment_query = """
                  INSERT IGNORE INTO comment (post_id, subreddit_id, username, created_time) 
                  VALUES (%s, %s, %s, %s)
              """
  
              cursor.execute(comment_query, comment_values)
              self.conn.commit()
              new_id = cursor.lastrowid
              if new_id == 0:
                  id_query = """
                      SELECT comment_id 
                      FROM comment 
                      WHERE post_id = %s
                      AND username = %s
                      AND created_time = %s
                  """
                  id_values = (post[rc.POST_ID],
                               comment[rc.USER_KEY],
                               comment[rc.CREATED_TIMESTAMP_KEY])
                  cursor.execute(id_query, id_values)
                  new_id = cursor.next()[0]
              comment[rc.COMMENT_ID] = new_id
  
              comment_history_values = (comment[rc.COMMENT_ID],
                                        comment[rc.VOTE_KEY],
                                        comment[rc.MESSAGE_KEY])
              comment_history_query = """
                  INSERT INTO comment_history (comment_id, votes, message, updated_time) 
                  (SELECT %s, %s, %s, NOW())  
              """
              cursor.execute(comment_history_query, comment_history_values)
              self.conn.commit()
          self.logger.info('Inserted comments from {} post successfully'.format(post[rc.POST_REF_KEY]))
  
  
  def mysql_test_suite():
      import csv
      with MysqlManager(rc.DATABASE_NAME,
                                rc.DB_USERNAME,
                                rc.DB_PASSWORD,
                                logging.getLogger('test')) as mysql_test:
  
          with open("testfiles/posts.csv", "r") as f:
              reader = csv.DictReader(f)
              posts = list(reader)
  
          mysql_test.insert_subreddits(posts)
          mysql_test.insert_posts(posts)
  
          for post_test in posts:
              assert post_test[rc.SUBREDDIT_ID] > 0
              assert post_test[rc.POST_ID] > 0
  
          with open("testfiles/comments.csv", "r") as f:
              reader = csv.DictReader(f)
              comments = list(reader)
  
          mysql_test.insert_comments(comments, posts[0])
  
          for comment in comments:
              assert comment[rc.COMMENT_ID] > 0
  
  
  if __name__ == "__main__":
      mysql_test_suite()
      print("All mysql tests ran successfully")
  

Answer 1

Next（）在mysql连接器Python中似乎不可用，但在mysql连接器库中可用

pip uninstall mysql-connector-python
pip install mysql-connector

Answer 2

正如roganjosh提到的，next（）不是一个属性。我改用fetchone（）。