blog新地址 Surprised => indulgence => Calm => Lost => Panic =>Calm

Python连接MySQL数据库

MySQL admin 1068℃ 0评论

使用Python进行MySQL转sqlite,需提前知道数据库格式

import MySQLdb
import sqlite3
import os
import time

def getDataFromMysql(dbname, table_name):
	# mysql
	conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='tweets', port=3306)
	cur = conn.cursor()

	# sqlite3
	conn_sqlite = sqlite3.connect(dbname)
	cursor_sqlite = conn_sqlite.cursor()

	select_sql_mysql = "select * from "+table_name
	cur.execute(select_sql_mysql)
	rows = cur.fetchall()
	count = 0
	insert_sql_sqlite = "insert into "+table_name+"(id, tweet_id, tweet_text, tweet_created_at, user_name, user_id,location, reply_tweet_id, reply_user_name, reply_user_id, reply_tweet_text, reply_tweet_created_at, reply_user_location) values(?,?,?,?,?,?,?,?,?,?,?,?,?)"
	for row in rows:
		row = list(row)
		for i in range(1, 13):
			row[i] = str(row[i]).decode("utf-8", "ignore")
		cursor_sqlite.execute(insert_sql_sqlite,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12]))

	conn_sqlite.commit()
	cursor_sqlite.close()
	cursor_sqlite.close()

	cur.close()
	conn.close()
	print "Mysql goto sqlite3 success!"

def createDatabaseSqlite(dbname, table_name):
	if os.path.exists(dbname):
		print("Your database is exists, and the programming is dropping it now!")
		os.remove(dbname)
	conn_sqlite = sqlite3.connect(dbname)
	cursor_sqlite = conn_sqlite.cursor()
	create_sql = " CREATE TABLE "+table_name+"(\
	`id` int(11) NOT NULL DEFAULT '0', \
	`tweet_id` varchar(255) DEFAULT NULL,\
	`tweet_text` varchar(255) DEFAULT NULL,\
	`tweet_created_at` datetime DEFAULT NULL,\
	`user_name` varchar(255) DEFAULT NULL,\
	`user_id` varchar(255) DEFAULT NULL,\
	`location` varchar(48) DEFAULT NULL,\
	`reply_tweet_id` varchar(255) DEFAULT NULL,\
	`reply_user_name` varchar(255) DEFAULT NULL,\
	`reply_user_id` varchar(255) DEFAULT NULL,\
	`reply_tweet_text` varchar(255) DEFAULT NULL,\
	`reply_tweet_created_at` datetime DEFAULT NULL,\
	`reply_user_location` varchar(48) DEFAULT NULL,\
	PRIMARY KEY (`id`)\
	)"
	cursor_sqlite.execute(create_sql)
	conn_sqlite.commit()
	cursor_sqlite.close()
	cursor_sqlite.close()
	print "Your database "+dbname+" has been created!"
	print "And the table "+table_name+" has been created!"

if __name__ == '__main__':
    '''
	for i in range(2, 10):
		start_time = time.time()
		dbname = "tweets_2010_0"+str(i)+".db"
		table_name = "tweets_2010_0"+str(i)
		createDatabaseSqlite(dbname, table_name)
		getDataFromMysql(dbname, table_name)
		end_time = time.time()
		the_time = end_time - start_time
		print dbname+" took time = "+str(the_time)
	print "All have been finished!"
	'''
	dbname = "tweets_2010_10.db"
	table_name = "tweets_2010_10"
	createDatabaseSqlite(dbname, table_name)
	getDataFromMysql(dbname, table_name)
	# dbname = "tweets_2010_01.db"
	# table_name = "tweets_2010_01"
	# createDatabaseSqlite(dbname, table_name)
	# getDataFromMysql(dbname, table_name)

 

 

 

转载请注明:Imekaku-Blog » Python连接MySQL数据库

喜欢 (0)or分享 (0)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址