I have a data frame result which I need to insert into existing table line by line, How will I insert result into a specific column name - imgtext

table structure

As in SQL I know I can write query as -

INSERT INTO tableName(imgtext) VALUES('Learn MySQL INSERT Statement');

Python script:

1. This code takes some value from csv and return some data with help of beautifulsoup
2. resulted data will save in csv

Problem:

1. Now rather than saving into csv how will I inserted resulted data into SQL table in specific column name imgtext

seeking solution:

How will I process csv data using data frame for inserting result into SQL rather than CSV.

`

 
img_text_list = []
 
df1 = pd.DataFrame(
    columns=['imgtext'])
img_formats = [".jpg", ".jpeg"]

df = pd.read_csv("urls.csv")
urls = df["urls"].tolist()

for y in urls:
    response = requests.get(y)

    soup = BeautifulSoup(response.text, 'html.parser')
    img_tags = soup.find_all('img', class_='pick')

    img_srcs = ["https://myimpact.in/"   img['src'].replace(
        '\\', '/') if img.has_attr('src') else '-' for img in img_tags]
    

    for count, x in enumerate(img_srcs): 

        if x != '-': 
            if pathlib.Path(x).suffix in img_formats:
                response = requests.get(x) 
            img = Image.open(io.BytesIO(response.content))
            text = pt.image_to_string(img, lang="hin") 

            # how to insert this text value into sql table column name - imgtext
            img_text_list.append(text) 
 
 
df1['img_text'] = img_text_list
  
df1.to_csv('data.csv', encoding='utf-8')

`

CodePudding user response：

to add value from CSV to your SQL table you will need to use a Python SQL Driver (pyodbc). Please see the sample code for connecting python to SQL.

sample code:

import pyodbc
import pandas as pd

server = 'yourservername' 
database = 'yourdatabasename' 
username = 'username' 
password = 'yourpassword' 

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=' server ';DATABASE=' database ';UID=' username ';PWD='  password)
cursor = cnxn.cursor()
# Insert Dataframe into SQL Server:
for index, row in df.iterrows():
     cursor.execute("Insert your QUERY here")
cnxn.commit()
cursor.close()

Prequisite:

Please install the pyodbc package here https://mkleehammer.github.io/pyodbc/

Reference:

https://learn.microsoft.com/en-us/sql/machine-learning/data-exploration/python-dataframe-sql-server?view=sql-server-ver16