在 python 中读取外部 sql 脚本

我正在学习如何在 Python 中执行 SQL (我知道 SQL，但不知道 Python)。

我有一个外部的 sql 文件，它创建并将数据插入到三个表“ Zookeep”、“ Handles”和“ Animal”中。

然后我还有一系列的查询要处理。下面的查询位于我加载到 python 脚本顶部的 zookeeper.sql 文件中。前两个例子是:

--1.1


SELECT ANAME,zookeepid
FROM ANIMAL, HANDLES
WHERE AID=ANIMALID;

——1.2

SELECT ZNAME, SUM(TIMETOFEED)
FROM ZOOKEEPER, ANIMAL, HANDLES
WHERE AID=ANIMALID AND ZOOKEEPID=ZID
GROUP BY zookeeper.zname;

这些都在 SQL 中执行得很好。现在我需要在 Python 内部执行它们。我已经给出并完成了代码，在文件中读取。然后执行循环中的所有查询。

1.1和1.2让我很困惑。我相信在循环中，这一行是我应该放入一些东西来运行第一个和第二个查询的地方。

Result = c.execute (“ SELECT * FROM% s;”% table) ;

但是什么？我想我漏掉了一些很明显的东西。我觉得是% table 让我不舒服。在查询1.1和1.2中，我不创建表，而是查找查询结果。

下面是我的全部 Python 代码。

import sqlite3
from sqlite3 import OperationalError


conn = sqlite3.connect('csc455_HW3.db')
c = conn.cursor()


# Open and read the file as a single buffer
fd = open('ZooDatabase.sql', 'r')
sqlFile = fd.read()
fd.close()


# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')


# Execute every command from the input file
for command in sqlCommands:
# This will skip and report errors
# For example, if the tables do not yet exist, this will skip over
# the DROP TABLE commands
try:
c.execute(command)
except OperationalError, msg:
print "Command skipped: ", msg




# For each of the 3 tables, query the database and print the contents
for table in ['ZooKeeper', 'Animal', 'Handles']:




**# Plug in the name of the table into SELECT * query
result = c.execute("SELECT * FROM %s;" % table);**


# Get all rows.
rows = result.fetchall();


# \n represents an end-of-line
print "\n--- TABLE ", table, "\n"


# This will print the name of the columns, padding each name up
# to 22 characters. Note that comma at the end prevents new lines
for desc in result.description:
print desc[0].rjust(22, ' '),


# End the line with column names
print ""
for row in rows:
for value in row:
# Print each value, padding it up with ' ' to 22 characters on the right
print str(value).rjust(22, ' '),
# End the values from the row
print ""


c.close()
conn.close()
245695 次浏览
小开

Your code already contains a beautiful way to execute all statements from a specified sql file

# Open and read the file as a single buffer
fd = open('ZooDatabase.sql', 'r')
sqlFile = fd.read()
fd.close()


# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')


# Execute every command from the input file
for command in sqlCommands:
# This will skip and report errors
# For example, if the tables do not yet exist, this will skip over
# the DROP TABLE commands
try:
c.execute(command)
except OperationalError, msg:
print("Command skipped: ", msg)

Wrap this in a function and you can reuse it.

def executeScriptsFromFile(filename):
# Open and read the file as a single buffer
fd = open(filename, 'r')
sqlFile = fd.read()
fd.close()


# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')


# Execute every command from the input file
for command in sqlCommands:
# This will skip and report errors
# For example, if the tables do not yet exist, this will skip over
# the DROP TABLE commands
try:
c.execute(command)
except OperationalError, msg:
print("Command skipped: ", msg)

To use it

executeScriptsFromFile('zookeeper.sql')

You said you were confused by

result = c.execute("SELECT * FROM %s;" % table);

In Python, you can add stuff to a string by using something called string formatting.

You have a string "Some string with %s" with %s, that's a placeholder for something else. To replace the placeholder, you add % ("what you want to replace it with") after your string

ex:

a = "Hi, my name is %s and I have a %s hat" % ("Azeirah", "cool")
print(a)
>>> Hi, my name is Azeirah and I have a Cool hat

Bit of a childish example, but it should be clear.

Now, what

result = c.execute("SELECT * FROM %s;" % table);

means, is it replaces %s with the value of the table variable.

(created in)

for table in ['ZooKeeper', 'Animal', 'Handles']:




# for loop example


for fruit in ["apple", "pear", "orange"]:
print(fruit)
>>> apple
>>> pear
>>> orange

If you have any additional questions, poke me.

小开

according me, it is not possible

solution:

  1. import .sql file on mysql server

  2. after

    import mysql.connector
import pandas as pd

    and then you use .sql file by convert to dataframe

小开

A very simple way to read an external script into an sqlite database in python is using executescript():

import sqlite3


conn = sqlite3.connect('csc455_HW3.db')


with open('ZooDatabase.sql', 'r') as sql_file:
conn.executescript(sql_file.read())


conn.close()
小开

First make sure that a table exists if not, create a table then follow the steps.

import sqlite3
from sqlite3 import OperationalError


conn = sqlite3.connect('Client_DB.db')
c = conn.cursor()


def execute_sqlfile(filename):
    

c.execute("CREATE TABLE clients_parameters (adress text, ie text)")
#
fd = open(filename, 'r')
sqlFile = fd.readlines()
fd.close()
lvalues = [tuple(v.split(';')) for v in sqlFile[1:] ]
try:
#print(command)
c.executemany("INSERT INTO clients_parameters VALUES (?, ?)", lvalues)
except OperationalError as msg:
print ("Command skipped: ", msg)


execute_sqlfile('clients.sql')


print(c.rowcount)

提交答案