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: Here is the current script I have: The script opens the directory and for each photo it takes the filename, size, type, height, width, DPI,

Python – Printing output into a SQL Table

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?

Answer

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.

Advertisement

Answer