Read MYSQL Table data from Python Code

-

First we need to import mysql module

Create connection to MySql Database and fetch the records.


Complete code given below-

 import mysql.connector

from mysql import connector


conn = mysql.connector.connect(host='127.0.0.1',database='firstschema',user='root',password='2511')

cursor=conn.cursor()

cursor.execute('SELECT * from firstschema.check1')

values=cursor.fetchall()

print(values)

Comments

Post a Comment

Popular posts from this blog

[Solved]SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape

-
 Solution - CASE1- If you are working with file path location for example- xlrd.open_workbook('C:\Users\xyz\Python_Local\File1.xls') Then it will throw the "Unicode"  error as  \U represents the Unicode escape because this is normal string not raw string. So use raw string to avoid this error by following methods- (r'C:\Users\xyz\Python_Local\File1.xls') ('C:\\Users\\xyz\\Python_Local\\File1.xls') ('C:/Users/xyz/Python_Local/File1.xls') CASE2- If we use normal string to print for example- print('Hello World") then it is fine If we want special character as normal character for example- print('It's world') # will throw error  so we have use '\'  escape  character to make ' normal character. print('It\'s world')
Read more

Read the excel and insert data into My SQL using Python

-
Install xlrd package Code- import xlrd import mysql.connector from mysql import connector # Open the workbook and define the worksheet book = xlrd.open_workbook(r'C:\Users\xyz\Python_Local\File1.xls') sheet = book.sheet_by_name("Sheet1") # Establish a MySQL connection conn = mysql.connector.connect(host='127.0.0.1',database='firstschema',user='root',password='2511') # Get the cursor, which is used to traverse the database, line by line cursor = conn.cursor() # Create the INSERT INTO sql query query = """INSERT INTO firstschema.tbl1 (name, mobile, location) VALUES (%s, %s, %s)""" # Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers for r in range(1, sheet.nrows): name = sheet.cell(r,0).value mobile     = sheet.cell(r,1).value location    = sheet.cell(r,2).value # Assign values from each row values = (name, mobile, location) # Execute...
Read more