Python - Printing output into a SQL Table

Question

I have a python script that can extract the metadata from pictures in a directory. As of right now they go into a text file in table format and the current output looks like this:

Filename                        |File Size(In Bytes)            |File Type                   |Image DPI                    |Image Height                  |Image Width                 |Image Format                |Image Mode                  |Image Frames                |
|X01CJ0050.JPG                  |1567529                        |.JPG                           |(300.0, 300.0)                 |400                            |600                            |JPEG                           |RGB                            |1                               |
|X01CJ0051.JPG                  |1567607                        |.JPG                           |(300.0, 300.0)                 |400                            |600                            |JPEG                           |RGB                            |1                               |
|X01CJ0072.JPG                  |1027986                        |.JPG                           |(72.0, 72.0)                   |400                            |600                            |JPEG                           |RGB                            |1                               |
|X01CJ0074.JPG                  |306064                         |.JPG                           |(72.0, 72.0)                   |400                            |600                            |JPEG                           |RGB                            |1

Here is the current script I have:

import json
import pathlib
from PIL import Image
from PIL.ExifTags import TAGS
import os
import os.path
import PIL
from pandas import json_normalize
import sqlalchemy
import pandas as pd

PIL.Image.MAX_IMAGE_PIXELS = 384000000

rootdir = r

newfile = newfile = open('meta.txt', 'w')
newfile.write("Filename                        |File Size(In Bytes)            |File     Type                   |Image DPI                    |Image Height                  |Image Width                 |Image Format                |Image Mode                  |Image Frames                |\n")
for file in os.listdir(rootdir):
    try:
        # read the image data using PIL
        image = Image.open(os.path.join(rootdir, file))

        # extract other basic metadata
        info_dict = {
            "Filename": os.path.basename(image.filename),
            "File Size": os.path.getsize(image.filename),
            "File Extension": pathlib.Path(image.filename).suffix,
            "Image DPI": image.info['dpi'],
            "Image Height": image.height,
            "Image Width": image.width,
            "Image Format": image.format,
            "Image Mode": image.mode,
            "Frames in Image": getattr(image, "n_frames", 1)
        }

        line = ""
        for label, value in info_dict.items():
            line += f"|{str(value):<30} "
        line += " |"
        newfile.write(line + '\n')
    except:
        # read the image data using PIL
        image = Image.open(os.path.join(rootdir, file))

        # extract other basic metadata
        info_dict = {
            "Filename": os.path.basename(image.filename),
            "Image Height": image.height,
            "Image Width": image.width,
            "Image Format": image.format,
            "Image Mode": image.mode,
            "Frames in Image": getattr(image, "n_frames", 1)
        }

        line = ""
        for label, value in info_dict.items():
            line += f"|{str(value):<30} "
        line += " |"
        newfile.write(line + '\n')
    
#Connect to the database
testDBCon = sqlalchemy.create_engine('mssql+pyodbc://SRVISCDB,58837/testDB? 
driver=SQL+Server+Native+Client+11.0')

#Choose what query to select a column from
query = "SELECT * FROM testDB.dbo.SuspensiaImageDetails;"

The script opens the directory and for each photo it takes the filename, size, type, height, width, DPI, mode and Frames. And as I stated it prints into a text file. Instead of going to a print file I would like for the output to be put into a SQL table that is already created. I have some lines of code that connects me to the database table and those are:

#Connect to the database
testDBCon = sqlalchemy.create_engine('mssql+pyodbc://SRVISCDB,58837/testDB? 
driver=SQL+Server+Native+Client+11.0')

#Choose what query to select a column from
query = "SELECT * FROM testDB.dbo.SuspensiaImageDetails;"

So what I would like to know is, How do i print the output to the table?

Toyeesh · Accepted Answer · 2022-07-19 17:31:17Z

2

Assuming you already have the table created according to the convention followed while printing the data, a simple INSERT query should suffice.

That being said, instead of line += " |" you would have to use line += ","

Following your convention, it should go something like :

query = "INSERT INTO testDB.dbo.SuspensiaImageDetails VALUES ("
query += line
query += ");"

You can't Print stuff into a mysql db per se.

edited Jul 19, 2022 at 17:31

answered Jul 19, 2022 at 17:21

Toyeesh

1346 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Edward Wynman Over a year ago

would this go into its respected column?

Toyeesh Over a year ago

It will if the correct ordering is followed (I have assumed it is the same as your print output)

Edward Wynman Over a year ago

I go to use it, I don't get an error however it didn't work. Could you will me maybe why?

Toyeesh Over a year ago

Hmm...... did you create a table first inside the database ?

CarlosSR · Accepted Answer · 2022-07-19 17:18:51Z

1

You are almost there. You have created an engine and now you need to create a connection, open a cursor and perform the query you want.

connection = testDBCon.connect()
cur = conn.cursor()

cur.execute(your sql) ##INSERT if you want to add data to an existing table, CREATE if it is a new table or SELECT if you just want to get data from db

conn.commit() ##to commit changes to database

answered Jul 19, 2022 at 17:18

CarlosSR

1,2051 gold badge13 silver badges24 bronze badges

1 Comment

Edward Wynman Over a year ago

so i would use cur.insert to add things

Collectives™ on Stack Overflow

Python - Printing output into a SQL Table

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related